SQL查询分页分页(SQL语法系列)

来源：互联网发布：淘宝激活码编辑：程序博客网时间：2024/05/16 15:02

1、存储过程：

CREATE PROCEDURE [USP_Product_GetPaged]    @WhereClause VARCHAR (2000),    @OrderBy VARCHAR (2000),    @PageIndex INT,    @PageSize INTAS    BEGIN        DECLARE @PageLowerBound INT, @PageUpperBound INT        SET @PageLowerBound = @PageSize * @PageIndex        SET @PageUpperBound = @PageLowerBound + @PageSize        CREATE TABLE #PageIndex        (            [IndexID] INT IDENTITY (1, 1) NOT NULL,            [ID] INT         )        DECLARE @SQL AS NVARCHAR(4000)        SET @SQL = 'INSERT INTO #PageIndex ([ID])'        SET @SQL = @SQL + ' SELECT'        IF @PageSize > 0            SET @SQL = @SQL + ' TOP ' + CONVERT(NVARCHAR, @PageUpperBound)        SET @SQL = @SQL + ' [ID]'        SET @SQL = @SQL + ' FROM [dbo].[LbtProjectInfo]'        IF LEN(@WhereClause) > 0            SET @SQL = @SQL + ' WHERE ' + @WhereClause        IF LEN(@OrderBy) > 0            SET @SQL = @SQL + ' ORDER BY ' + @OrderBy        EXEC (@SQL)        SELECT            TempTable.[ID],             TempTable.[ProjectName],             TempTable.[ContractNumber]        FROM            [dbo].[LbtProjectInfo] TempTable        INNER JOIN            #PageIndex PageIndex        ON            TempTable.[ID] = PageIndex.[ID]        WHERE            PageIndex.IndexID > @PageLowerBound        AND            PageIndex.IndexID <= @PageUpperBound        ORDER BY            PageIndex.IndexID        SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'        SET @SQL = @SQL + ' FROM [dbo].[LbtProjectInfo]'        IF LEN(@WhereClause) > 0            SET @SQL = @SQL + ' WHERE ' + @WhereClause        EXEC (@SQL)    ENDGO

执行存储分页：

----分页(第一页)EXECUTE  dbo.USP_Product_GetPaged '', '[ID] ASC', 0, 10----分页(第二页)EXECUTE [USP_Product_GetPaged] '', '[ID] ASC', 1, 10----分页(第一页)EXECUTE [USP_Product_GetPaged] '[ID] > 600', '[ID] ASC', 0, 10----分页(第二页)EXECUTE [USP_Product_GetPaged] '[ID] > 600', '[ID] ASC', 1, 10

第二种方法(row_number() over(order by ... desc))：

SELECT TOP (10)     [t].[ID] AS [ID],     [t].[ProjectName] AS [ProjectName]FROM (    SELECT        [ID] AS [ID],        [ProjectName] AS [ProjectName],        ROW_NUMBER() OVER (ORDER BY [ID] ASC) AS [row_number]    FROM [dbo].[LbtProjectInfo]    WHERE [ID] >= 1)  AS [t]WHERE [t].[row_number] > 0ORDER BY [t].[ID] ASC

第三种(sql server2012新语法)：

利用offset...fetch分页：

SELECT [ID],[ProjectName]FROM [dbo].[LbtProjectInfo]ORDER BY [ID]OFFSET 10 ROWSFETCH NEXT 10 ROWS ONLY

阅读全文

0 0