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 Server¡¢Oracle¡¢MySQL¡¢Sybase ÒÔ¼° Access µÈµÈ¡£
¶ÔÓÚÄÇЩϣÍûÔÚÊý¾Ý¿âÖд洢Êý¾Ý²¢´ÓÖлñÈ¡Êý¾ÝµÄÈËÀ´Ëµ£¬SQL µÄ֪ʶÊǼÛÖµÎÞ·¨ºâÁ¿µÄ¡£
ʲôÊÇ SQL?
SQL Ö¸½á¹¹»¯²éѯÓïÑÔ ......
ÔÚAsp.netºóÓкܶೣÓõıí½á¹¹ membership¾ÍÊÇÒ»¸öµäÐÍ£¬³ý´Ë»¹ÓÐcode±í
CREATE TABLE [dbo].[CodeTables](
[CodeTableID] [uniqueidentifier] NOT NULL,
[ParentCodeTableID] [uniqueidentifier] NULL,
[Name] [varchar](200) COLLATE SQL_Latin1_General_ ......
sql Óï¾ä°´¹¦ÄÜ·ÖΪ3ÀࣺÊý¾Ý¶¨ÒåÓï¾ä£¬Êý¾Ý²Ù×÷Óï¾ä£¬Êý¾Ý¿ØÖÆÓï¾ä
Ò»£ºÊý¾Ý¶¨ÒåÓï¾ä£º
CREATE TABLE --´´½¨Ò»¸öÊý¾Ý¿â±í
DROP TABLE --´ÓÊý¾Ý¿âÖÐɾ³ý±í
ALTER TABLE --ÐÞ¸ÄÊý¾Ý¿â±í½á¹¹
&n ......
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') --· ......
²Î¿¼£ºhttp://gaowenjie7758.blog.163.com/blog/static/5946788920091116244306/
SQL´æ´¢¹ý³ÌÖÐSELECTÓëSET ¶Ô±äÁ¿¸³ÖµµÄÇø±ð
SQL Server ÖжÔÒѾ¶¨ÒåµÄ±äÁ¿¸³ÖµµÄ·½Ê½ÓÃÁ½ÖÖ£¬·Ö±ðÊÇ SET ºÍ SELECT¡£¶ÔÓÚÕâÁ½ÖÖ·½Ê½µÄÇø±ð£¬SQL Server Áª»ú´ÔÊéÖÐÒѾÓÐÏêϸµÄ˵Ã÷£¬µ«ºÜ¶àʱºòÎÒÃDz¢Ã»ÓÐ×¢Ò⣬ÆäʵÕâÁ½ÖÖ·½Ê½»¹ÊÇÓкܶ ......