sql 2005中apply的二个应用

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