Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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 Server Ë÷Òý½á¹¹¼°ÆäʹÓã¨Ò»£©

SQL Server Ë÷Òý½á¹¹¼°ÆäʹÓã¨Ò»£©
×÷Õߣºfreedk
Ò»¡¢ÉîÈëdz³öÀí½âË÷Òý½á¹¹
¡¡¡¡Êµ¼ÊÉÏ£¬Äú¿ÉÒÔ°ÑË÷ÒýÀí½âΪһÖÖÌØÊâµÄĿ¼¡£Î¢ÈíµÄSQL SERVERÌṩÁËÁ½ÖÖË÷Òý£º¾Û¼¯Ë÷Òý£¨clustered index£¬Ò²³Æ¾ÛÀàË÷Òý¡¢´Ø¼¯Ë÷Òý£©ºÍ·Ç¾Û¼¯Ë÷Òý£¨nonclustered index£¬Ò²³Æ·Ç¾ÛÀàË÷Òý¡¢·Ç´Ø¼¯Ë÷Òý£©¡£ÏÂÃ棬ÎÒÃǾÙÀýÀ´ËµÃ÷һϾۼ¯Ë÷ ......

SQLÓï¾ä´´½¨Êý¾Ý¿â

1.create database dataname
ÕâÊÇ´´½¨Êý¾Ý¿â×î¼òµ¥µÄ·½·¨£®Êý¾Ý¿âµÄ¸÷¸öÊôÐÔ¶¼ÊÇĬÈÏ£®ÈçÊý¾Ý¿âÎļþÓëÈÕÖ¾Îļþ´æ´¢Ä¿Â¼£®Êý¾Ý¿â´óСµÈ£®
ÏÂÃæ½éÉÜϳ£Óþö¶¨Êý¾Ý¿âÊôÐÔµÄ×Ӿ䣮
on:¼òµ¥Àí½âΪ¶¨Òå´æ´¢Êý¾Ý¿âÎļþµÄλÖ㬿´ÏÂÃæ´úÂ룮
filename:Êý¾Ý¿âµÄÂß¼­±ðÃû
size:Êý¾Ý¿â³õʼ´óС
maxsize:Êý¾Ý¿â³õ´óÈÝÁ¿
fil ......

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ºÅ