哪位告诉我一个SQLSERVER分页语句

发布网友发布时间：2022-04-08 19:15

共2个回答

热心网友时间：2022-04-08 20:44

MS-SQL 2005
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
备注
ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
SELECT ROW_NUMBER() OVER(ORDER BY PRICE DESC) AS [ROW],* FROM PRODUCTS

热心网友时间：2022-04-08 22:02

我给你一下存储过程
CREATE PROCEDURE [dbo].[sp_SystemQuotationRevisionPaging]
(
@ClientID NVARCHAR(255),
@ClientModel NVARCHAR(255),
@Collection NVARCHAR(255),
@startIndexpage int,
@endIndexpage int,
@Count INT OUTPUT
)
As
SELECT
*
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY CreatedDate DESC) AS row,* FROM QuotationRevision
WHERE
UPPER(ClientID) LIKE @ClientID +'%'
AND UPPER(ClientModel) LIKE @ClientModel +'%'
AND (UPPER([Collection]) LIKE '%'+@Collection +'%'
OR
(@Collection = '' AND [Collection] is null))
) QuotationRevision
WHERE row BETWEEN (@endIndexpage-1)*@startIndexpage+1 AND @endIndexpage*@startIndexpage

SET @Count=(SELECT COUNT(*) FROM QuotationRevision
WHERE
UPPER(ClientID) LIKE @ClientID+'%'
AND UPPER(ClientModel) LIKE @ClientModel+'%'
AND (UPPER([Collection]) LIKE '%'+@Collection +'%'
OR
(@Collection = '' AND [Collection] is null))
)
RETURN

GO