好久没有用SqlServer了,今天写了一个分页,遇到了小问题,本着温故而知新的道理,再来随便写些什么吧。
语句是这样的
string sql=“select * from ( select*,(row_number() over(order by Creattime desc)) as rownumber from VolunteerInfo u" + querysql + " order by " + sort + order + ") WHERE rownumber between @startNum and @endNum and t.State=0”
炸一看,这从逻辑上是可以的,没道理出错啊,但事实是他确实存在问题:
报的错为:消息 1033,级别 15,状态 1,第 1 行
除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
后来才知道这个原因是:原因就是针对一个表的SELECT其实并不是返回一个表,而是一个游标。如果一定要用怎么办呢?答案就是配合 TOP 100 PERCENT
即改为:
select * from (select TOP 100000 *,(row_number() over(order by Creattime desc)) as rownumber from VolunteerInfo u" + querysql + " order by " + sort + order + ") WHERE rownumber between @startNum and @endNum and t.State=0
此时还会报错:where条件有错,rownumber 无效,此时解决就很简单了 用as关键字给子查询赋个值就ok
最终语句为: string sql = "select * from (select TOP 100000 *,(row_number() over(order by Creattime desc)) as rownumber from VolunteerInfo u" + querysql + " order by " + sort + order + ") as t WHERE t.rownumber between @startNum and @endNum and t.State=0 ";
最后再附一个常用的分页查询语句的连接吧:http://www.jb51.net/article/35213.htm