SQL Server中的分页

sqlserver2000时的分页思路
1.分页查询时,首先将数据排序
    select * from MyStudent order by fid desc
2.取第一页数据
    select Top 5 * from MyStudent order by fid desc
3.取第二页数据
    select Top 5 * from MyStudent 
    where fid not in
    (
        select Top 5 * from MyStudent order by fid desc
    )
    order by fid desc
4.取第三页的数据
    select Top 5 * from MyStudent 
    where fid not in
    (
        select Top (2*5) * from MyStudent order by fid desc
    )
    order by fid desc
4.取第四页的数据
    select Top 5 * from MyStudent 
    where fid not in
    (
        select Top (3*5) * from MyStudent order by fid desc
    )
    order by fid desc
    
sqlserver2005时的分页思路    

--第7页数据
select * from
(
    select *,row_number() over(order by fid desc) as runumber
    from MyStudent
) as tbl
where tbl.rnumber between 6*5+1 and 7*5

--第20页数据
select * from
(
    select *,row_number() over(order by fid desc) as runumber
    from MyStudent
) as tbl
where tbl.rnumber between 19*5+1 and 20 *5


--开窗函数与聚合函数一起使用,可以让聚合函数对每一条数据都计算一次
select *,count(*) over() as 总条数 from MyStudent 

 

上一篇:innerText 与innerHTML区别


下一篇:利用ImageMagick命令执行漏洞拿下Facebook四万美元奖金