declare @sql=N'SELECT TOP '+cast(@pageSize as varchar(50))+' * FROM (SELECT TOP '
+cast(@pageSize*@currentpage as varchar(50))+' * FROM [InMail] A ORDER BY A.id DESC) B ORDER BY B.id ASC'
set @sql='SELECT FROM ('+@sql+') C ORDER BY C.id DESC'先是分面。但是后面又出来了排序不正确了。
如1
2
3
4
5
6
7
8
9下面是最新的。要显是在最上面
如果pageSize=2 currentPage=2
则要求取出
7
6
----------
pageSize=2 currentPage=3
5
4
+cast(@pageSize*@currentpage as varchar(50))+' * FROM [InMail] A ORDER BY A.id DESC) B ORDER BY B.id ASC'
set @sql='SELECT FROM ('+@sql+') C ORDER BY C.id DESC'先是分面。但是后面又出来了排序不正确了。
如1
2
3
4
5
6
7
8
9下面是最新的。要显是在最上面
如果pageSize=2 currentPage=2
则要求取出
7
6
----------
pageSize=2 currentPage=3
5
4
IF OBJECT_ID('[InMail]') IS NOT NULL
DROP TABLE [InMail]
GO
CREATE TABLE [InMail] ([ID] [int])
INSERT INTO [InMail]
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '7' UNION ALL
SELECT '8' UNION ALL
SELECT '9'----SELECT * FROM [InMail]-->SQL查询如下:
DECLARE @sql NVARCHAR(4000), @pageSize INT, @currentpage INT
SELECT @pageSize = 2, @currentpage = 3
SET @sql = N'SELECT TOP '+CAST(@pageSize AS VARCHAR(50))+' * FROM (SELECT TOP '
+CAST(@pageSize*@currentpage AS VARCHAR(50))+
' * FROM [InMail] A ORDER BY A.id DESC) B ORDER BY B.id ASC'
SET @sql = 'SELECT * FROM ('+@sql+') C ORDER BY C.id DESC'
EXEC (@sql)
/*
ID
-----------
5
4(2 行受影响)
*/
SELECT @pageSize = 2, @currentpage = 2
SET @sql = N'SELECT TOP '+CAST(@pageSize AS VARCHAR(50))+' * FROM (SELECT TOP '
+CAST(@pageSize*@currentpage AS VARCHAR(50))+
' * FROM [InMail] A ORDER BY A.id DESC) B ORDER BY B.id ASC'
SET @sql = 'SELECT * FROM ('+@sql+') C ORDER BY C.id DESC'
EXEC (@sql)
/*
ID
-----------
7
6(2 行受影响)
*/
IF OBJECT_ID('[InMail]') IS NOT NULL
DROP TABLE [InMail]
GO
CREATE TABLE [InMail] ([ID] [int])
INSERT INTO [InMail]
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '7' UNION ALL
SELECT '8' UNION ALL
SELECT '9'----SELECT * FROM [InMail]-->SQL查询如下:
DECLARE @pageSize INT, @currentpage INT
SELECT @pageSize = 2, @currentpage = 3
;WITH t AS
(
SELECT rn=ROW_NUMBER()OVER(ORDER BY id DESC),*
FROM [InMail]
)
SELECT ID
FROM t
WHERE rn BETWEEN @pageSize*(@currentpage-1)+1 AND @pageSize*@currentpage
/*
ID
-----------
5
4(2 行受影响)
*/