关于分页这点事 -- 如何才能分得准
一直以来, 大家热衷于存储过程分页谁分得快, 但是我们搜索一下"通用分页存储过程", 就会发现这个有问题的方法流传得颇广
对于通用查询的解决方案, 两层TOP
SELECT * FROM
(
SELECT TOP(PageSize) * FROM
(
SELECT TOP (PageSize * (PageIndex + 1)) *
FROM table
ORDER BY field DESC
)
ORDER BY field ASC
)
ORDER BY field DESC
这个号称"掐头去尾"的分页方法, 其实有很多问题
首先就是, 在最后一页时, 如果符合此页的数据不足PageSize时, 会取到前一页的部分数据, 因为你总是取到了PageSize条记录呢, 解决办法呢当初是先计算ItemCount, 再决定外面这层Top的数量.
其次, 当orderby field不是unique的时候(这种情况很常见, 因为web上很多grid的都有点击列头排序的功能), 第二次的Top未必就是子查询里的最后那些数据.
为什么会是这样呢, 我们知道, 对于任何两次查询, sql server返回的数据顺序是一样的, 但是对一个子查询做倒排序, 得到的结果集未必就是原始结果集的倒置
举个例子:
create table test (a varchar(10), b varchar(10))
insert into test values ('a','1')
insert into test values ('b','1')
insert into test values ('c','3')
insert into test values ('d','4')
insert into test values ('e','4')
insert into test values ('f','4')
select top 6 * from test order by b asc
a b
--------------------
b 1
a 1
c 3
f 4
e 4
d 4
我们想着倒着再来一次top 2应该是e d两条吧, 那么来吧
select top 2 * from
(select top 6 * from test
order by b asc)
as a order by b desc
a b
--------------------
f 4
e 4
很不幸, 实际结果是f, e
也就是每次它排序的结果是确定的, 但倒排序的时候未必是按正排序的反序来排列数据的, 而上述分页方法恰恰是建立在这个不成立的假设上的, 所以对于order by field不唯一的查询, 这种分页方法一定不对
那么有没有解决方案呢? 答案也是肯定的, 把主键或其它唯一性字段加到order by中去
select * from test
select top 2 * from
(select top 6 * from test
order by b asc,a desc)
as a order by b desc,a asc
这样就暂时OK了, 但是, 我们知道有时候查询结果确实会因为join的存在, 而出现根本无法给出唯一性字段(当然这种情况非常少), 这时这个分页方案就彻底没戏了
再来看一个查询方案, not in
SELECT TOP (PageSize) *
FROM table
WHERE (ID NOT IN
(SELECT TOP (PageSize * PageIndex) id
FROM table
ORDER BY field))
ORDER BY field
这个没问题了, 因为始终依据的是同一种排序方法, 对于最后一页的查询, 也同样能够给出正确的记录数, 但是我们要看到这种分页方法的前提是需要给出唯一性字段ID来做not in操作的, 这是它的局限性
不妨多看一个类似的, > max
SELECT TOP (PageSize) *
FROM table
WHERE (ID >
(SELECT MAX(ID)
FROM (SELECT TOP (PageSize * PageIndex) id
FROM table
ORDER BY field) AS T))
ORDER BY field
同样, 需要ID的存在, 如果只是用order by field来代替ID, 那么结果就错得很离谱了, 因为可能大量的field = max(field)的值还没有被显示, 就被无情的>掉了
顺便看一下sql server2005的CTE(common table expression)的方法, 其它说是CTE方法不准确, 应该说row_number函数好一些
with t as(
select row_number() over(order by field) as rownum, * from table
) select * from t where rownum .....
CTE改进的方法同样需要有ID这样的唯一字段的存在
with t as(
select row_number() over(order by field) as rownum, id from table
) select a.* from table a inner join t on a.id = b.id where t.rownum .....
我们这里不研究效率, 因为效率这里有人研究:分页实现方法的性能比较 , 这种方法就不会出现分不准的问题了, 对于无唯一字段的查询也可以解决, 为什么我说这种不能叫CTE分页法呢, 因为这样写也是可以的:
select *from( select row_number() over(order by field) as rownum, * from table ) as a where a.rownum ....
顺便我比对了一下, 和CTE的执行计划是一样的
那么在sql server2000中, 没有唯一性字段下如何要分得准? 临时表, identity字段, 这种方法网上一搜也是遍地都是
对于通用查询的解决方案, 两层TOP
SELECT * FROM
(
SELECT TOP(PageSize) * FROM
(
SELECT TOP (PageSize * (PageIndex + 1)) *
FROM table
ORDER BY field DESC
)
ORDER BY field ASC
)
ORDER BY field DESC
这个号称"掐头去尾"的分页方法, 其实有很多问题
首先就是, 在最后一页时, 如果符合此页的数据不足PageSize时, 会取到前一页的部分数据, 因为你总是取到了PageSize条记录呢, 解决办法呢当初是先计算ItemCount, 再决定外面这层Top的数量.
其次, 当orderby field不是unique的时候(这种情况很常见, 因为web上很多grid的都有点击列头排序的功能), 第二次的Top未必就是子查询里的最后那些数据.
为什么会是这样呢, 我们知道, 对于任何两次查询, sql server返回的数据顺序是一样的, 但是对一个子查询做倒排序, 得到的结果集未必就是原始结果集的倒置
举个例子:
create table test (a varchar(10), b varchar(10))
insert into test values ('a','1')
insert into test values ('b','1')
insert into test values ('c','3')
insert into test values ('d','4')
insert into test values ('e','4')
insert into test values ('f','4')
select top 6 * from test order by b asc
a b
--------------------
b 1
a 1
c 3
f 4
e 4
d 4
我们想着倒着再来一次top 2应该是e d两条吧, 那么来吧
select top 2 * from
(select top 6 * from test
order by b asc)
as a order by b desc
a b
--------------------
f 4
e 4
很不幸, 实际结果是f, e
也就是每次它排序的结果是确定的, 但倒排序的时候未必是按正排序的反序来排列数据的, 而上述分页方法恰恰是建立在这个不成立的假设上的, 所以对于order by field不唯一的查询, 这种分页方法一定不对
那么有没有解决方案呢? 答案也是肯定的, 把主键或其它唯一性字段加到order by中去
select * from test
select top 2 * from
(select top 6 * from test
order by b asc,a desc)
as a order by b desc,a asc
这样就暂时OK了, 但是, 我们知道有时候查询结果确实会因为join的存在, 而出现根本无法给出唯一性字段(当然这种情况非常少), 这时这个分页方案就彻底没戏了
再来看一个查询方案, not in
SELECT TOP (PageSize) *
FROM table
WHERE (ID NOT IN
(SELECT TOP (PageSize * PageIndex) id
FROM table
ORDER BY field))
ORDER BY field
这个没问题了, 因为始终依据的是同一种排序方法, 对于最后一页的查询, 也同样能够给出正确的记录数, 但是我们要看到这种分页方法的前提是需要给出唯一性字段ID来做not in操作的, 这是它的局限性
不妨多看一个类似的, > max
SELECT TOP (PageSize) *
FROM table
WHERE (ID >
(SELECT MAX(ID)
FROM (SELECT TOP (PageSize * PageIndex) id
FROM table
ORDER BY field) AS T))
ORDER BY field
同样, 需要ID的存在, 如果只是用order by field来代替ID, 那么结果就错得很离谱了, 因为可能大量的field = max(field)的值还没有被显示, 就被无情的>掉了
顺便看一下sql server2005的CTE(common table expression)的方法, 其它说是CTE方法不准确, 应该说row_number函数好一些
with t as(
select row_number() over(order by field) as rownum, * from table
) select * from t where rownum .....
CTE改进的方法同样需要有ID这样的唯一字段的存在
with t as(
select row_number() over(order by field) as rownum, id from table
) select a.* from table a inner join t on a.id = b.id where t.rownum .....
我们这里不研究效率, 因为效率这里有人研究:分页实现方法的性能比较 , 这种方法就不会出现分不准的问题了, 对于无唯一字段的查询也可以解决, 为什么我说这种不能叫CTE分页法呢, 因为这样写也是可以的:
select *from( select row_number() over(order by field) as rownum, * from table ) as a where a.rownum ....
顺便我比对了一下, 和CTE的执行计划是一样的
那么在sql server2000中, 没有唯一性字段下如何要分得准? 临时表, identity字段, 这种方法网上一搜也是遍地都是
