sqlserver的几个分页语句

sqlserver的几个分页语句

1. 使用内置的ROW_NUMBER() OVER() 函数

SELECT TOP 页大小 * 
FROM
    (
        SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
    )   as A  
WHERE RowNumber > 页大小*(页数-1)
//例如：
select top 50 * from 
(select row_number()over(order by id)rownumber,* from [dbo].[MtBusiDeal]) a
where rownumber>50

注意ROW_NUMBER() OVER()是sql 2005以后才有的函数，兼容上需要注意

2.TOP/MAX 实现分页

SELECT TOP 页大小 *
FROM table1
WHERE id >
        (
            SELECT ISNULL(MAX(id),0)
            FROM
                (
                    SELECT TOP 页大小*(当前页-1) id FROM table1 ORDER BY id 
                ) as A
           )
ORDER BY id
//例如：
SELECT TOP 50 *
FROM [MtBusiDeal]
WHERE id >
        (
            SELECT ISNULL(MAX(id),0)
            FROM
                (
                    SELECT TOP 0 id FROM [MtBusiDeal] ORDER BY id 
                ) as A
           )
ORDER BY id

这个方案虽然解决了兼容的问题，而且效率也很高。但是有个问题，如果一个递增的id这个方案是不完美的

3.TOP/NOT IN 实现

SELECT TOP 页大小 *
FROM table1
WHERE id NOT IN 
        (
           SELECT TOP 页大小*(当前页-1) id FROM table1 ORDER BY id
        )
ORDER BY id

//例如
SELECT TOP 50 *
FROM [MtBusiDeal]
WHERE id NOT IN
        (

           SELECT TOP 0 id FROM [MtBusiDeal] ORDER BY id
        )
ORDER BY id

这种方法虽然效率略低，但是实现兼容和根据非递增键分页的功能，也许能用到

总结：哪个好合适用哪个 (ง •̀_•́)ง┻━┻