使用ROW_NUMBER时从SQL Server返回总记录 [英] Return total records from SQL Server when using ROW_NUMBER
本文介绍了使用ROW_NUMBER时从SQL Server返回总记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想返回数据库中的记录总数,以便设置分页.在SQL Server 2008中使用以下分页方法时,如何返回数据库中的记录总数?
ALTER PROCEDURE [dbo].[Nop_LoadAllOptimized]
(
@PageSize int = 20,
@PageNumber int = 1,
@WarehouseCombinationID int = 1,
@CategoryId int = 58,
@OrderBy int = 0,
@TotalRecords int = null OUTPUT
)
AS
BEGIN
WITH Paging AS (
SELECT rn = (ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @OrderBy = 0 AND @CategoryID IS NOT NULL AND @CategoryID > 0
THEN pcm.DisplayOrder END ASC,
CASE WHEN @OrderBy = 0
THEN p.[Name] END ASC,
CASE WHEN @OrderBy = 5
THEN p.[Name] END ASC,
CASE WHEN @OrderBy = 10
THEN wpv.Price END ASC,
CASE WHEN @OrderBy = 15
THEN wpv.Price END DESC,
CASE WHEN @OrderBy = 20
THEN wpv.Price END DESC,
CASE WHEN @OrderBy = 25
THEN wpv.UnitPrice END ASC
)), p.*, pcm.DisplayOrder, wpv.Price, wpv.UnitPrice FROM Nop_Product p
INNER JOIN Nop_Product_Category_Mapping pcm ON p.ProductID=pcm.ProductID
INNER JOIN Nop_ProductVariant pv ON p.ProductID = pv.ProductID
INNER JOIN Nop_ProductVariant_Warehouse_Mapping wpv ON pv.ProductVariantID = wpv.ProductVariantID
WHERE pcm.CategoryID = @CategoryId AND (wpv.Published = 1 AND pv.Published = 1 AND p.Published = 1 AND p.Deleted = 0)
AND wpv.WarehouseID IN (select WarehouseID from Nop_WarehouseCombination where UserWarehouseCombinationID = @WarehouseCombinationID)
)
SELECT TOP (@PageSize) * FROM Paging PG
WHERE PG.rn > (@PageNumber * @PageSize) - @PageSize
SET @TotalRecords = @@ROWCOUNT
END
解决方案
我通常是这样做的-从性能角度来看,从来没有真正检查过它是否非常有效:
WITH YourCTE AS
(
SELECT
(list of columns),
ROW_NUMBER() OVER (ORDER BY ......) AS 'RowNum'
FROM dbo.YourBaseTable
)
SELECT
*,
(SELECT MAX(RowNum) FROM YourCTE) AS 'TotalRows'
FROM
YourCTE
WHERE
RowNum BETWEEN 101 AND 150
基本上,RowNum
值在行总数中将具有值1(如果CTE中没有PARTITION BY
),因此选择MAX(RowNum)
,您将获得总行数. /p>
I would like to return the total number of records in the database so I can set up pagination. How do I return the total number of records in the DB when using the following paging method in SQL Server 2008?
ALTER PROCEDURE [dbo].[Nop_LoadAllOptimized]
(
@PageSize int = 20,
@PageNumber int = 1,
@WarehouseCombinationID int = 1,
@CategoryId int = 58,
@OrderBy int = 0,
@TotalRecords int = null OUTPUT
)
AS
BEGIN
WITH Paging AS (
SELECT rn = (ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @OrderBy = 0 AND @CategoryID IS NOT NULL AND @CategoryID > 0
THEN pcm.DisplayOrder END ASC,
CASE WHEN @OrderBy = 0
THEN p.[Name] END ASC,
CASE WHEN @OrderBy = 5
THEN p.[Name] END ASC,
CASE WHEN @OrderBy = 10
THEN wpv.Price END ASC,
CASE WHEN @OrderBy = 15
THEN wpv.Price END DESC,
CASE WHEN @OrderBy = 20
THEN wpv.Price END DESC,
CASE WHEN @OrderBy = 25
THEN wpv.UnitPrice END ASC
)), p.*, pcm.DisplayOrder, wpv.Price, wpv.UnitPrice FROM Nop_Product p
INNER JOIN Nop_Product_Category_Mapping pcm ON p.ProductID=pcm.ProductID
INNER JOIN Nop_ProductVariant pv ON p.ProductID = pv.ProductID
INNER JOIN Nop_ProductVariant_Warehouse_Mapping wpv ON pv.ProductVariantID = wpv.ProductVariantID
WHERE pcm.CategoryID = @CategoryId AND (wpv.Published = 1 AND pv.Published = 1 AND p.Published = 1 AND p.Deleted = 0)
AND wpv.WarehouseID IN (select WarehouseID from Nop_WarehouseCombination where UserWarehouseCombinationID = @WarehouseCombinationID)
)
SELECT TOP (@PageSize) * FROM Paging PG
WHERE PG.rn > (@PageNumber * @PageSize) - @PageSize
SET @TotalRecords = @@ROWCOUNT
END
解决方案
I typically do it this way - never really checked whether it's very efficient from a performance point of view:
WITH YourCTE AS
(
SELECT
(list of columns),
ROW_NUMBER() OVER (ORDER BY ......) AS 'RowNum'
FROM dbo.YourBaseTable
)
SELECT
*,
(SELECT MAX(RowNum) FROM YourCTE) AS 'TotalRows'
FROM
YourCTE
WHERE
RowNum BETWEEN 101 AND 150
Basically, the RowNum
value will have values 1 through the total of rows (if you don't have a PARTITION BY
in your CTE) and thus selecting MAX(RowNum)
, you get the total number of rows.
这篇关于使用ROW_NUMBER时从SQL Server返回总记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文