Ôںܶà±à³ÌÓïÑÔÖж¼ÓÐ 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
²Î¿¼£ºhttp://gaowenjie7758.blog.163.com/blog/static/5946788920091116244306/
SQL´æ´¢¹ý³ÌÖÐSELECTÓëSET ¶Ô±äÁ¿¸³ÖµµÄÇø±ð
SQL Server ÖжÔÒѾ¶¨ÒåµÄ±äÁ¿¸³ÖµµÄ·½Ê½ÓÃÁ½ÖÖ£¬·Ö±ðÊÇ SET ºÍ SELECT¡£¶ÔÓÚÕâÁ½ÖÖ·½Ê½µÄÇø±ð£¬SQL Server Áª»ú´ÔÊéÖÐÒѾÓÐÏêϸµÄ˵Ã÷£¬µ«ºÜ¶àʱºòÎÒÃDz¢Ã»ÓÐ×¢Ò⣬ÆäʵÕâÁ½ÖÖ·½Ê½»¹ÊÇÓкܶ ......