apply是sql 2005中新加的几个新东西的其中之一.以前一直以为他与join的区别不大,最近研究了一下,感觉很不错,发现其在计算每组top值和计算当前值,当前值的前一个值和当前值的后一个值都有很好的使用,而且在所有方法中,处了row_Number()以外是效率最高的,同时也是写法最简单的一种,下面是我写的二个简单的例子
新闻类表
CREATE TABLE [dbo].[NewsClass]( [NewsClassID] [int] IDENTITY(1,1) NOT NULL, /*标识种子*/ [NewsClassName] [nvarchar](50) NULL, /*类别名称*/ ) insert into newsclass(newsClassName)values('第一类')
insert into newsclass(newsClassName)values('第二类')
insert into newsclass(newsClassName)values('第三类')
insert into newsclass(newsClassName)values('第四类')
新闻表CREATE TABLE [dbo].[News]( [NewsID] [int] IDENTITY(1,1) NOT NULL, /*标识种子*/ [NewsTitle] [nvarchar](50) NULL, /*标题*/ [NewsClassID] [int] NULL, /*类别*/ [NewsSubmitTime] [datetime] NULL, /*时间*/ [NewsAuthorEmail] [nvarchar](50) NULL, /*email*/ [NewsContent] [int] NULL, /*新闻内容的字数*/ ) INSERT INTO News(NewsTitle,NewsClassID,NewsSubmitTime,NewsAuthorEmail,NewsContent)VALUES('第一类第一条',1,'2011-07-29 01:01:02.000','1',100)
INSERT INTO News(NewsTitle,NewsClassID,NewsSubmitTime,NewsAuthorEmail,NewsContent)VALUES('第一类第二条',1,'2011-07-29 01:01:03.000','1',20)
INSERT INTO News(NewsTitle,NewsClassID,NewsSubmitTime,NewsAuthorEmail,NewsContent)VALUES('第一类第三条',1,'2011-07-29 01:01:04.000','1',80)
INSERT INTO News(NewsTitle,NewsClassID,NewsSubmitTime,NewsAuthorEmail,NewsContent)VALUES('第二类第一条',2,'2011-07-29 01:01:05.000','1',10)
INSERT INTO News(NewsTitle,NewsClassID,NewsSubmitTime,NewsAuthorEmail,NewsContent)VALUES('第二类第二条',2,'2011-07-29 01:01:06.000','1',30)
INSERT INTO News(NewsTitle,NewsClassID,NewsSubmitTime,NewsAuthorEmail,NewsContent)VALUES('第二类第三条',2,'2011-07-29 01:01:07.000','1',60)
INSERT INTO News(NewsTitle,NewsClassID,NewsSubmitTime,NewsAuthorEmail,NewsContent)VALUES('第三类第一条',3,'2011-07-29 01:01:08.000','1',50)
INSERT INTO News(NewsTitle,NewsClassID,NewsSubmitTime,NewsAuthorEmail,NewsContent)VALUES('第三类第二条',3,'2011-07-29 01:01:09.000','1',20)
INSERT INTO News(NewsTitle,NewsClassID,NewsSubmitTime,NewsAuthorEmail,NewsContent)VALUES('第四类第一条',4,'2011-07-29 01:01:10.000','1',10)
此处说明一点,新闻表中.newscontent字段正式是内容,可是为了体现 top n的功能,我就给改成int型用来存数字了.
一,显示一下 分组 top n的功能
SELECT newsClassid,newsClassName,t.newstitle,t.newscontent FROM NewsClass AS nc CROSS apply ( SELECT TOP (2) newsID,newstitle,newscontent FROM News WHERE newsclassid=nc.newsclassid ORDER BY newscontent desc ) t
显示结果1 第一类 第一类第一条 100 1 第一类 第一类第三条 80 2 第二类 第二类第三条 60 2 第二类 第二类第二条 30 3 第三类 第三类第一条 50 3 第三类 第三类第二条 20 4 第四类 第四类第一条 10
我们可以看到,每一类的前二条就全出来,这个应用在计算成绩和业绩的时候,很好用哦.
二,计算当前值,当前值的上一条,当前的下一条(同类别)
SELECT pre.newstitle,pre.newsclassid,pre.newscontent, newes.NewsTitle,newes.newsclassid,newes.newscontent, bac.newstitle,bac.newsclassid,bac.newscontent FROM News AS newes OUTER apply ( SELECT TOP (1) NewsTitle,newsclassid,newscontent FROM News AS n WHERE n.NewsClassID=newes.NewsClassID AND n.NewsSubmitTime<newes.NewsSubmitTime ORDER BY NewsSubmitTime desc ) AS pre OUTER apply ( SELECT TOP (1) NewsTitle,newsclassid,newscontent FROM News AS n WHERE n.NewsClassID=newes.NewsClassID AND n.NewsSubmitTime>newes.NewsSubmitTime ORDER BY NewsSubmitTime ) AS bac
结果:
NULL NULL NULL 第一类第一条 1 100 第一类第二条 1 20 第一类第一条 1 100 第一类第二条 1 20 第一类第三条 1 80 第一类第二条 1 20 第一类第三条 1 80 NULL NULL NULL NULL NULL NULL 第二类第一条 2 10 第二类第二条 2 30 第二类第一条 2 10 第二类第二条 2 30 第二类第三条 2 60 第二类第二条 2 30 第二类第三条 2 60 NULL NULL NULL NULL NULL NULL 第三类第一条 3 50 第三类第二条 3 20 第三类第一条 3 50 第三类第二条 3 20 NULL NULL NULL NULL NULL NULL 第四类第一条 4 10 NULL NULL NULL