Max分页、With as内存表 存储过程

没办法，这个高亮貌似无法识别SQL语句。

ALTER PROCEDURE [MySchema].[StaNumDiscoverAnaly] (@start int, @pageSize int, @staSel nvarchar(200), @dateS nvarchar(20), @dateE nvarchar(20), @caseId nvarchar(10)) AS DECLARE @strSql nvarchar(3000); DECLARE @tableName nvarchar(20); SELECT @tableName=BillBase FROM table_name WHERE CID=@caseId; DECLARE @where nvarchar(300); set @where = N' WHERE (CallStation IN(' + @staSel + N') OR RespStation IN(' + @staSel +N'))'; if LEN(@dateS)>0 begin set @where = @where + ' AND (CallDate>=''' + @dateS + ''')'; end if LEN(@dateE)>0 begin set @where = @where + ' AND (CallDate<=''' + @dateE + ''')'; end set @strSql = N'(SELECT CallNumber, CallPIN, RespNumber, RespPIN, CallDate, CallStation, RespStation FROM ' + @tableName + @where + ')'; --ORDER BY CallStation, CallNumber, CallDate --不同的排序顺序，会有不同的效果，可以任意交换下CallStation、CallNumber顺序看看 if @start < 0 set @start = 0; else set @start = (@start-1) * @pageSize; set @strSql = N'WITH TempT(CallNumber,CallPIN,RespNumber,RespPIN,CallDate,CallStation,RespStation) AS ' + @strSql+ N',Temp2(Number,PIN,Station) AS( SELECT CallNumber,CallPIN,CallStation FROM TempT UNION ALL SELECT RespNumber,RespPIN,RespStation FROM TempT ), Temp3 (Number,PIN,Station,TIMES)AS(SELECT * , COUNT(*)AS TIMES FROM Temp2 GROUP BY Station,Number,PIN),'+ N' UsefulT(ID,Number,PIN,Station,TIMES) AS (SELECT row_number() over (order by Station) as ID,Number,PIN,Station,TIMES FROM Temp3) SELECT TOP('+ CAST(@pageSize AS nvarchar(10)) + ') ID, Number,PIN,Station,TIMES FROM UsefulT WHERE ID > (SELECT MAX(ID) FROM(SELECT TOP(' + CAST(@start AS nvarchar(20)) +N') ID FROM UsefulT ORDER BY ID ) AS T) ORDER BY ID'; PRINT @strSql; EXEC (@strSql); RETURN 

在做的过程中因为有些地方需要处理之前得到的一个临时表，但是如果保存在临时表中的话会导致额外的空间消耗，而且我们这种大数据量如果过多的IO操作会让速度变的很慢，所以放弃了临时表，而使用了With As创建表。

语法是：With 表名(表中各种新旧列) AS ( select语句得到的表 )

具体的可以参考http://jeemiss.javaeye.com/blog/642519

由存储过程中可以看出，可以在一个with语句中创建多个内存表(暂时这么叫吧)，而且后面的内存表可以使用前面的表，不过有一定的约束条件，比如中间要用逗号隔开、select语句不能出现Order By、with语句之后只有第一个select语句可以使用上面定义的临时表等等。

 

分页算法用的是Max算法，没有用Not IN的原因是效果不好。参见http://blog.sina.com.cn/s/blog_53a185ad010000z8.html

Max算法在做的时候也出现很多问题，比如我在内存表中查处来新表后，每一列都有很多的重复行出现，因为在Union连接的时候用了All，所以过多的重复行无法成功的获得Top或者Max，，苦啊。。

不过后来实验室的一个师弟搞到了一个方法，就是用Group by语句把某两列合并成一列来作为排序列

比如Group By A+B，这样连接A列和B列，就可以获得一个唯一的列C

但是这样操作我总感觉不是太爽，因为合并后毕竟还需要自己处理分解开。

 

顺便说下Group by的威力吧。So牛B。Group by可以把指定的列汇总成一行，并可以用聚合函数进行合并统计

 

select A+B as C from xx Group by A+B

select  A as D,Count(*)As sum From xx Group by A

-- -- -- -- --

A   B C D sum

1 11 111 1 1

2 22 222 2 2

2 33 233

 

具体可以参考http://blog.csdn.net/vividboy/archive/2008/04/21/2313023.aspx。

 

后来我就寻思啊，这如果能自己生成一列，然后自动添加序列多好，从1/2/3/4..这样。然后我就找啊找啊，，就遇见了Row_Number()这个牛逼的函数，我了个去啊...。正合适么这不是，函数就是给你添加一列，然后用1起始的数字填充并自加1。

select Row_Number() over (Order by xx) As ID, AA, BB from table

这样以后，就从你select语句原来只有AA，BB两列的前面添加了一列ID，从1开始自加，，多好！你说好不好？这样以后，就可以为所欲为了，不管你前面table被整的多乱，我这边一下给你捋直了~站好！。当然，这个table可以是你前面定义的内存表，然后你再把这个有序号的新查询结果with成一个内存表，这样一来，后面爱做啥你就做啥吧，尽情的分页好了，有这么一个免费的ID列作为主键也好，Order by也兆，成功也就是一会儿的事。

在分页的时候，注意ID>xxx那里，xxx里的查询结果必须加一个AS T，这样才能和ID进行比较，不然不行的..。

 

恩分页暂时就这样，第一个相对大点的存储过程出炉，哎就是记录一下...顺便收集下技术文档。 共勉。