Sql Serverシリーズ:ページング文の問合せ
19000 ワード
1テンポラリ・テーブルによるページング
ページング・ストレージ・プロシージャ:CREATE PROCEDURE [USP_Product_GetPaged]
@WhereClause VARCHAR (2000),
@OrderBy VARCHAR (2000),
@PageIndex INT,
@PageSize INT
AS
BEGIN
DECLARE @PageLowerBound INT, @PageUpperBound INT
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize
CREATE TABLE #PageIndex
(
[IndexID] INT IDENTITY (1, 1) NOT NULL,
[ProductID] INT
)
DECLARE @SQL AS NVARCHAR(4000)
SET @SQL = 'INSERT INTO #PageIndex ([ProductID])'
SET @SQL = @SQL + ' SELECT'
IF @PageSize > 0
SET @SQL = @SQL + ' TOP ' + CONVERT(NVARCHAR, @PageUpperBound)
SET @SQL = @SQL + ' [ProductID]'
SET @SQL = @SQL + ' FROM [dbo].[Product]'
IF LEN(@WhereClause) > 0
SET @SQL = @SQL + ' WHERE ' + @WhereClause
IF LEN(@OrderBy) > 0
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
EXEC (@SQL)
SELECT
TempTable.[ProductID],
TempTable.[ProductCode],
TempTable.[ProductName],
TempTable.[CategoryID],
TempTable.[UnitPrice]
FROM
[dbo].[Product] TempTable
INNER JOIN
#PageIndex PageIndex
ON
TempTable.[ProductID] = PageIndex.[ProductID]
WHERE
PageIndex.IndexID > @PageLowerBound
AND
PageIndex.IndexID <= @PageUpperBound
ORDER BY
PageIndex.IndexID
SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
SET @SQL = @SQL + ' FROM [dbo].[Product]'
IF LEN(@WhereClause) > 0
SET @SQL = @SQL + ' WHERE ' + @WhereClause
EXEC (@SQL)
END
GO
ページング・ストアド・プロシージャの例:-- Where
EXECUTE [USP_Product_GetPaged] '', '[ProductID] ASC', 0, 10
EXECUTE [USP_Product_GetPaged] '', '[ProductID] ASC', 1, 10
-- Where
EXECUTE [USP_Product_GetPaged] '[UnitPrice] > 20', '[UnitPrice] DESC', 0, 10
EXECUTE [USP_Product_GetPaged] '[UnitPrice] > 20', '[UnitPrice] DESC', 1, 10
2利用ROW_NUMBER()ページング
ROW_NUMBER()関数はSQL Server 2005に追加されたランキング関数であり、ROW_NUMBER()をページングします.SELECT TOP (10)
[t].[ProductID] AS [ProductID],
[t].[ProductName] AS [ProductName]
FROM (
SELECT
[ProductID] AS [ProductID],
[ProductName] AS [ProductName],
ROW_NUMBER() OVER (ORDER BY [ProductID] ASC) AS [row_number]
FROM [dbo].[Product]
WHERE [UnitPrice] > 20
) AS [t]
WHERE [t].[row_number] > 10
ORDER BY [t].[ProductID] ASC
3 OFFSETを利用して…FETCHページング
OFFSET...FETCHはSQL Server 2012に新たに追加された実装ページング方式であり、OFFSETはSELECTに対して何行スキップしたか、FETCHは特定の位置から何行検索したかを示す.
例:SELECT [ProductID],[CategoryID],[ProductCode],[ProductName],[UnitPrice]
FROM [dbo].[Product]
ORDER BY [ProductID]
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
OFFSETを使って...FETCH制限:
◊使用OFFSET...FETCH句はORDER BYを同時に使用する必要があります.
◊OFFSETは単独で使用できますが、FETCHは単独では使用できません.
◊SELECT TOPとOFFSETを併用してはいけません…FETCH.
CREATE PROCEDURE [USP_Product_GetPaged]
@WhereClause VARCHAR (2000),
@OrderBy VARCHAR (2000),
@PageIndex INT,
@PageSize INT
AS
BEGIN
DECLARE @PageLowerBound INT, @PageUpperBound INT
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize
CREATE TABLE #PageIndex
(
[IndexID] INT IDENTITY (1, 1) NOT NULL,
[ProductID] INT
)
DECLARE @SQL AS NVARCHAR(4000)
SET @SQL = 'INSERT INTO #PageIndex ([ProductID])'
SET @SQL = @SQL + ' SELECT'
IF @PageSize > 0
SET @SQL = @SQL + ' TOP ' + CONVERT(NVARCHAR, @PageUpperBound)
SET @SQL = @SQL + ' [ProductID]'
SET @SQL = @SQL + ' FROM [dbo].[Product]'
IF LEN(@WhereClause) > 0
SET @SQL = @SQL + ' WHERE ' + @WhereClause
IF LEN(@OrderBy) > 0
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
EXEC (@SQL)
SELECT
TempTable.[ProductID],
TempTable.[ProductCode],
TempTable.[ProductName],
TempTable.[CategoryID],
TempTable.[UnitPrice]
FROM
[dbo].[Product] TempTable
INNER JOIN
#PageIndex PageIndex
ON
TempTable.[ProductID] = PageIndex.[ProductID]
WHERE
PageIndex.IndexID > @PageLowerBound
AND
PageIndex.IndexID <= @PageUpperBound
ORDER BY
PageIndex.IndexID
SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
SET @SQL = @SQL + ' FROM [dbo].[Product]'
IF LEN(@WhereClause) > 0
SET @SQL = @SQL + ' WHERE ' + @WhereClause
EXEC (@SQL)
END
GO
-- Where
EXECUTE [USP_Product_GetPaged] '', '[ProductID] ASC', 0, 10
EXECUTE [USP_Product_GetPaged] '', '[ProductID] ASC', 1, 10
-- Where
EXECUTE [USP_Product_GetPaged] '[UnitPrice] > 20', '[UnitPrice] DESC', 0, 10
EXECUTE [USP_Product_GetPaged] '[UnitPrice] > 20', '[UnitPrice] DESC', 1, 10
ROW_NUMBER()関数はSQL Server 2005に追加されたランキング関数であり、ROW_NUMBER()をページングします.
SELECT TOP (10)
[t].[ProductID] AS [ProductID],
[t].[ProductName] AS [ProductName]
FROM (
SELECT
[ProductID] AS [ProductID],
[ProductName] AS [ProductName],
ROW_NUMBER() OVER (ORDER BY [ProductID] ASC) AS [row_number]
FROM [dbo].[Product]
WHERE [UnitPrice] > 20
) AS [t]
WHERE [t].[row_number] > 10
ORDER BY [t].[ProductID] ASC
3 OFFSETを利用して…FETCHページング
OFFSET...FETCHはSQL Server 2012に新たに追加された実装ページング方式であり、OFFSETはSELECTに対して何行スキップしたか、FETCHは特定の位置から何行検索したかを示す.
例:SELECT [ProductID],[CategoryID],[ProductCode],[ProductName],[UnitPrice]
FROM [dbo].[Product]
ORDER BY [ProductID]
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
OFFSETを使って...FETCH制限:
◊使用OFFSET...FETCH句はORDER BYを同時に使用する必要があります.
◊OFFSETは単独で使用できますが、FETCHは単独では使用できません.
◊SELECT TOPとOFFSETを併用してはいけません…FETCH.
SELECT [ProductID],[CategoryID],[ProductCode],[ProductName],[UnitPrice]
FROM [dbo].[Product]
ORDER BY [ProductID]
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY