Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQLÊý¾Ý¿âCTEµÄÓ÷¨

Ôںܶà±à³ÌÓïÑÔÖж¼ÓÐ forÑ­»·ÕâÑùµÄ¶«Î÷¡£ÔÚÊý¾Ý¿âÀïÃæ Ìæ´úËûÊÇ Óαê
µ«ÊÇÓαêʹÓÃÆðÀ´ÊÇÏ൱ºÄ·Ñ×ÊÔ´µÄ£¬½ñÌì¿´¼ûÒ»¸öCTE³¢ÊÔÁËÏÂËûµÄÓ÷¨
create table employewhere
(
 id int identity(1,1),
 [name] varchar(10),
 [value] varchar(10),
 [ttime] int
)
insert employewhere
select 'ÕÅÈý',2,1
union all
select 'ÕÅÈý',2,2
union all
select 'ÕÅÈý',2,3
union all
select 'ÕÅÈý',2,4
union all
select 'ÀîËÄ',2,1
union all
select 'ÀîËÄ',2,2
union all
select 'ÀîËÄ',2,3
union all
select 'ÀîËÄ',2,4
union all
select 'ÀîËÄ',2,1
insert employewhere
select 'ÍõÎå',2,1
union all
select 'ÍõÎå',2,3
union all
select 'ÍõÎå',2,4
ÎÒÏëµÃµ½ttimeΪÁ¬ÐøÊý×ÖµÄname
ÕÅÈý
ÀîËÄ
select * from  employewhere
1 ÕÅÈý 2 1
2 ÕÅÈý 2 2
3 ÕÅÈý 2 3
4 ÕÅÈý 2 4
5 ÀîËÄ 2 1
6 ÀîËÄ 2 2
7 ÀîËÄ 2 3
8 ÀîËÄ 2 4
9 ÍõÎå 2 1
10 ÍõÎå 2 3
11 ÍõÎå 2 4
12 ÍõÎå 2 1
13 ÍõÎå 2 3
14 ÍõÎå 2 4
15 ÍõÎå 2 1
16 ÍõÎå 2 3
17 ÍõÎå 2 4
-----------------------------
with myCTE as
(
 select id,[name],value,ttime ,1 as number   from employewhere where value=2
 union all
 select tt.id,tt.name,tt.value,tt.ttime ,number+1 from employewhere as tt
 inner join myCTE on myCTE.[name]=tt.[name] and tt.ttime=myCTE.ttime+1--Á¬½ÓÆðÀ´µÄÌõ¼þ
 where tt.value=2
)
select * from myCTE where number>3
8 ÀîËÄ 2 4 4
4 ÕÅÈý 2 4 4
µ«ÊÇΪʲôҪÕâÃ´Ð´ÄØ
ÎÒÃÇ¿ÉÒÔÕâôִÐвéѯÀïÃæµÄÊý¾Ý
with myCTE as
(
 select id,[name],value,ttime ,1 as number   from employewhere where value=2
 union all
 select tt.id,tt.name,tt.value,tt.ttime ,number+1 from employewhere as tt
 inner join myCTE on myCTE.[name]=tt.[name] and tt.ttime=myCTE


Ïà¹ØÎĵµ£º

SQL ³õ¼¶½Ì³Ì

ÊÇÓÃÓÚ·ÃÎʺʹ¦ÀíÊý¾Ý¿âµÄ±ê×¼µÄ¼ÆËã»úÓïÑÔ¡£
ͨ¹ý SQL À´¹ÜÀíÊý¾Ý
½á¹¹»¯²éѯÓïÑÔ (SQL) ÊÇÓÃÓÚ·ÃÎÊÊý¾Ý¿âµÄ±ê×¼ÓïÑÔ£¬ÕâЩÊý¾Ý¿â°üÀ¨ SQL Server¡¢Oracle¡¢MySQL¡¢Sybase ÒÔ¼° Access µÈµÈ¡£
¶ÔÓÚÄÇЩϣÍûÔÚÊý¾Ý¿âÖд洢Êý¾Ý²¢´ÓÖлñÈ¡Êý¾ÝµÄÈËÀ´Ëµ£¬SQL µÄ֪ʶÊǼÛÖµÎÞ·¨ºâÁ¿µÄ¡£
ʲôÊÇ SQL?
SQL Ö¸½á¹¹»¯²éѯÓïÑÔ ......

SQl Code±íÉè¼Æ

ÔÚAsp.netºóÓкܶೣÓõıí½á¹¹ membership¾ÍÊÇÒ»¸öµäÐÍ£¬³ý´Ë»¹ÓÐcode±í
CREATE TABLE [dbo].[CodeTables](
    [CodeTableID] [uniqueidentifier] NOT NULL,
    [ParentCodeTableID] [uniqueidentifier] NULL,
    [Name] [varchar](200) COLLATE SQL_Latin1_General_ ......

sql Óï¾ä·ÖÀà

sql Óï¾ä°´¹¦ÄÜ·ÖΪ3ÀࣺÊý¾Ý¶¨ÒåÓï¾ä£¬Êý¾Ý²Ù×÷Óï¾ä£¬Êý¾Ý¿ØÖÆÓï¾ä
Ò»£ºÊý¾Ý¶¨ÒåÓï¾ä£º
       CREATE TABLE --´´½¨Ò»¸öÊý¾Ý¿â±í
       DROP TABLE --´ÓÊý¾Ý¿âÖÐɾ³ý±í
      ALTER TABLE --ÐÞ¸ÄÊý¾Ý¿â±í½á¹¹
  &n ......

SQLʱ¼äº¯ÊýÏêϸ˵Ã÷

1. µ±Ç°ÏµÍ³ÈÕÆÚ¡¢Ê±¼ä
select getdate()
2. dateadd ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ
ÀýÈ磺ÏòÈÕÆÚ¼ÓÉÏ2Ìì
select dateadd(day,2,'2004-10-15') --·µ»Ø£º2004-10-17 00:00:00.000
3. datediff ·µ»Ø¿çÁ½¸öÖ¸¶¨ÈÕÆÚµÄÈÕÆÚºÍʱ¼ä±ß½çÊý¡£
select datediff(day,'2004-09-01','2004-09-18') --· ......

SQL´æ´¢¹ý³ÌÖÐSELECTÓëSET ¶Ô±äÁ¿¸³ÖµµÄÇø±ð

²Î¿¼£ºhttp://gaowenjie7758.blog.163.com/blog/static/5946788920091116244306/
SQL´æ´¢¹ý³ÌÖÐSELECTÓëSET ¶Ô±äÁ¿¸³ÖµµÄÇø±ð
SQL Server ÖжÔÒѾ­¶¨ÒåµÄ±äÁ¿¸³ÖµµÄ·½Ê½ÓÃÁ½ÖÖ£¬·Ö±ðÊÇ SET ºÍ SELECT¡£¶ÔÓÚÕâÁ½ÖÖ·½Ê½µÄÇø±ð£¬SQL Server Áª»ú´ÔÊéÖÐÒѾ­ÓÐÏêϸµÄ˵Ã÷£¬µ«ºÜ¶àʱºòÎÒÃDz¢Ã»ÓÐ×¢Ò⣬ÆäʵÕâÁ½ÖÖ·½Ê½»¹ÊÇÓкܶ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ