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

SQL Server2005 applyµÄһЩÔËÓÃ

Àý×Ó£ºÕÒ³öÿ¸ö¸èÊÖÏúÁ¿×î¸ßµÄ3Ê׸è
create table singer_info
(
id int primary key identity(1,1),
name varchar(20),
song varchar(20),
amount int
)
 
insert into singer_info values('jay','aaa',1000)
insert into singer_info values('jay','bbb',2000)
insert into singer_info values('jay','ccc',3000)
insert into singer_info values('jay','ddd',4000)
insert into singer_info values('jay','eee',5000)
insert into singer_info values('jay1','fff',1000)
insert into singer_info values('jay1','ggg',2000)
insert into singer_info values('jay1','hhh',3000)
insert into singer_info values('jay1','iii',4000)
insert into singer_info values('jay2','jjj',1000)
insert into singer_info values('jay2','kkk',2000)
insert into singer_info values('jay2','lll',3000)
insert into singer_info values('jay2','mmm',4000)
insert into singer_info values('jay3','nnn',1000)
 
(1)ʹÓÃfunction apply
create function gettop
(@name varchar(20))
returns table
as
return (select top(3)id,name,song,amount
from singer_info
where name = @name
order by amount desc)
 
select distinct b.id,b.name,b.song,b.amount
from singer_info a
cross apply
gettop(a.name)as b
 
(2)ʹÓÃapply
select distinct c.id,c.name,c.song,c.amount
from singer_info a
cross apply
(select top(3)id,name,song,amount
from singer_info b
where b.name = a.name
order by amount desc) as c
order by c.name asc,c.amount desc
 
(3)ʹÓÃover partition by
select * from
(select a.id,a.name,a.song,a.amount,
row_number() over(partition by a.name order by a.name,a.amount desc) rn
from singer_info a)b
where b.rn<=3


Ïà¹ØÎĵµ£º

SQL Server 2005 T SQL cross Apply Óëouter apply

SQL Server 2005 T-SQL Apply
͸¹ýÖ´Ðмƻ®¿ÉÒÔ¿´³ö£¬cross applyÀàËÆ²»´øwhereÌõ¼þµÄÁ¬½Ó¼´cross join £¨½»²æÁ¬½Ó¼´µÑ¿¨¶û»ý£º·µ»ØÐÐÊýΪ£ºÇ°±í·ûºÏÌõ¼þµÄÐгËÉϺó±í·ûºÏÌõ¼þµÄÐУ© ¡£ÐÎʽÉÏ»áÁé»îЩ.
ʹÓà APPLY ÔËËã·û¿ÉÒÔΪʵÏÖ²éѯ²Ù×÷µÄÍⲿ±í±í´ïʽ·µ»ØµÄÿ¸öÐе÷ÓñíÖµº¯Êý¡£±íÖµº¯Êý×÷ΪÓÒÊäÈ룬Íⲿ±í±í´ï ......

SQL Union ÓëUnion AllÏê½â

1.Union
UNION Ö¸ÁîµÄÄ¿µÄÊǽ«Á½¸ö SQL Óï¾äµÄ½á¹ûºÏ²¢ÆðÀ´¡£´ÓÕâ¸ö½Ç¶ÈÀ´¿´£¬ UNION ¸ú JOIN ÓÐЩÐíÀàËÆ£¬ÒòΪÕâÁ½¸öÖ¸Áî¶¼¿ÉÒÔÓɶà¸ö±í¸ñÖÐߢȡ×ÊÁÏ¡£ UNION µÄÒ»¸öÏÞÖÆÊÇÁ½¸ö SQL Óï¾äËù²úÉúµÄÀ¸Î»ÐèÒªÊÇͬÑùµÄ×ÊÁÏÖÖÀà¡£ÁíÍ⣬µ±ÎÒÃÇÓà UNIONÕâ¸öÖ¸Áîʱ£¬ÎÒÃÇÖ»»á¿´µ½²»Í¬µÄ×ÊÁÏÖµ (ÀàËÆ SELECT DISTINCT)¡£
UNION µ ......

SQLÖÐJOINºÍUNIONÇø±ð¡¢Ó÷¨¼°Ê¾Àý


1.JOINºÍUNIONÇø±ð
join ÊÇÁ½Õűí×ö½»Á¬ºóÀïÃæÌõ¼þÏàͬµÄ²¿·Ö¼Ç¼²úÉúÒ»¸ö¼Ç¼¼¯£¬
unionÊDzúÉúµÄÁ½¸ö¼Ç¼¼¯(×Ö¶ÎÒªÒ»ÑùµÄ)²¢ÔÚÒ»Æð£¬³ÉΪһ¸öеļǼ¼¯ ¡£
JOINÓÃÓÚ°´ÕÕONÌõ¼þÁª½ÓÁ½¸ö±í£¬Ö÷ÒªÓÐËÄÖÖ£º
INNER JOIN£ºÄÚ²¿Áª½ÓÁ½¸ö±íÖеļǼ£¬½öµ±ÖÁÉÙÓÐÒ»¸öͬÊôÓÚÁ½±íµÄÐзûºÏÁª½ÓÌõ¼þʱ£¬ÄÚÁª½Ó²Å·µ»ØÐС£ÎÒÀí½âµÄ ......

ʹÓù«Óñí±í´ïʽ£¨CTE£©¼ò»¯Ç¶Ì×SQL ºÍ½øÐеݹéµ÷ÓÃ

1.ʹÓÃCTE¼ò»¯Ç¶Ì×sql
   ÏÈ¿´ÏÂÃæÒ»¸öǶÌ׵IJéѯÓï¾ä£º
select * from person.StateProvince where CountryRegionCode in 
         (select CountryRegionCode from person.CountryRegion where  ......

case when then...else end sql Óï¾äÓ÷¨

select f.AGENTID as 'ID',
          f.AGENTNAME as 'ÐÕÃû',
          f.COMNAME as '¹«Ë¾¼ò³Æ',
          c.REGISTDATE as '×¢²áʱ¼ä',
     ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ