分页数据库实现 - 数据库编程

分页数据库实现

SQL 2000 查看第4页的内容

[sql] 
select top 5 * from customers where customerId not in  
(  
    --3*5=15  
    select top 15 customerId from customers order by customerid asc  
)  
order by customerid

SQL 2005 Row_Number函数简化实现

[sql] 
select * from   
(select row_number() over (order by sId asc) as num,* from student) as s  
where s.num between 6 and 10  order by sId asc

Over()就是传说中的”开窗函数”，本身聚合函数只会计算一次，开窗以后就可以为每条记录都计算一次聚合了。

Over子句可以为每一行计算表达式而不是只为一行，并且over可以单独定义窗口中的排序方式，而不影响最终结果集。例如：

select *,row_number() over(order byid asc) as hanghao from callrecordsorder by id desc