sql分页

-------------------------------------------------------------------
------------------------------sql分页------------------------------
-------------------------------------------------------------------
--方法1：语法（支持 desc/asc）
--SELECT TOP 页大小 * FROM table1
--WHERE id NOT IN (SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id desc)
--ORDER BY id
--示例
SELECT TOP 10 * FROM pdt_Music
WHERE MusicID NOT IN
             (SELECT TOP 100 MusicID FROM pdt_Music ORDER BY MusicID desc)
ORDER BY MusicID desc
-------------------------------------------------------------------
-------------------------------------------------------------------
--方法2：语法（支持：asc 不支持：desc）
--SELECT TOP 页大小 * FROM table1
--WHERE id > (SELECT ISNULL(MAX(id),0) FROM (SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id desc) A)
--ORDER BY id desc


--示例
SELECT TOP 10 * FROM pdt_Music
WHERE MusicID > (SELECT MAX(MusicID) FROM (SELECT TOP 100 MusicID FROM pdt_Music order by MusicID asc) as A)
 ORDER BY MusicID asc
-------------------------------------------------------------------
-------------------------------------------------------------------
--方法3：语法
--SELECT TOP 页大小 * 
--FROM (SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1) A 
--WHERE RowNumber > 页大小*(页数-1)
--示例


SELECT TOP 10 * FROM 
(SELECT ROW_NUMBER() OVER (ORDER BY MusicID desc) AS RowNumber,* FROM pdt_Music) A
WHERE RowNumber > 100