新闻动态
sql2005分页
https://www.sytm.net 发布日期:2010/7/1 13:05:31

1、为什么要使用row方案:
在oracle里有row_number虚列,
mySql有limit关键字分页,
他们都有一个比较通用的分页方案,
使得hibernate等类似的程序可以拼接sql字符串提供通用的分页。
而sqlserver却没有这样的分页方案。
稍稍改装row_number()over(order by )用法,获得了一个通用的分页方案。
如提供了sql如下:
SQL code
select * from Student where Age>18 order by Age
被row方案的分页程序处理后变成
在select 后面添加 top 开始位置 0 __tc,在外层嵌套固定模式的查询sql)
SQL code
select *
from (
    select row_number()over(order by __tc__)__rn__,*
    from (select top 开始位置+10 0 __tc__,* from Student where Age>18 order by Age)t
)tt
where __rn__>开始位置
这样就得到了拼接出通用的分页sql方案了。
2、row方案的排序:
row方案可以任意排序,
只要修改最内层的select排序即可,
应该来说是很简单易用的。
3、row方案和普通row_number()方案的区别:
一般的row方案:
SQL code
select *
from (select top 开始位置+10 row_number()over(order by Id)__rn__, * from Student)t
where __rn__>=开始的位置
使用了over(order by 表中的列),照成了必须由用户提供这个列,
而不容易使用分页程序生成分页sql(如hibernate分页)。
而row方案使用的是一个常数列tempColumn,值永远是0。
SQL code
select *
from (
  select row_number()over(order by TempColmun) *
  from (
    select top 开始的位置 0 as TempColmun,*
    from Student order by Id
  )tt)t
where rowNumber >=开始的位置
这个列是静态的,只是为了使用row_number()函数,
并不是真正的order by 依据,order by 实际看最内层。
我分析是因为row方案使用一个静态的列tempColumn,
这样可能被sql分析程序认为是无需排序的,省下了排序过程的开销。
4、数据测试:
 
SQL code
--插入测试数据200w条
create table Student(
  Id int PRIMARY KEY identity(1,1),
  Name nvarchar(50),
  Age int
)
insert Student(Name,Age)values('Name',18)
while (select count(*) from Student)<2000000
  insert Student select Name,Age from Student
运行测试代码:
SQL code
--开始测试查询
declare @now datetime
--max方案
select 'max'方案
select @now=getdate()
--begin
select top 10 * from Student
where Id>(
  select max(Id)
  from (
    select top 1999990 Id from Student order by Id)tt)
--end
declare @maxDiff int
select @maxDiff=datediff(ms,@now,getdate())
--top方案
select 'top'方案
select @now=getdate()
--begin
select top 10 * from Student
where Id not in(select top  1999990 Id from Student)
--end
declare @topDiff int
select @topDiff=datediff(ms,@now,getdate())
--row方案
select 'row'方案
select @now=getdate()
--begin
select *
from (
select row_number()over(order by tc)rn,*
from (select top 2000000 0 tc,* from Student)t
)tt
where rn>1999990
--end
declare @rowDiff int
select @rowDiff=datediff(ms,@now,getdate())
--row_number方案
select 'row_number'方案
select @now=getdate()
--begin
select *
from(
select top 2000000 row_number()over(order by Id)rn,* from Student
)t
where rn>1999990
--end
declare @row_numberDiff int
select @row_numberDiff=datediff(ms,@now,getdate())
--记录结果
select '第20万页'页码,@maxDiff max方案,@topDiff top方案,@rowDiff row方案,@row_numberDiff row_number方案
 

更多阅读
返回列表
© 2010 TianMei Technology All rights reserved. ICP:辽B2-20150138辽公网安备 21010202000010号  目录概览