Sql Paging
2009-08-31 19:10 蜡笔小旧 阅读(237) 评论(0) 编辑 收藏 举报以NorthWind为例:
(1)两次排序
CREATE PROC dbo.GetCategories @PageSize int, @PageNum int AS DECLARE @Ignore int DECLARE @LastID int IF @PageNum > 1 BEGIN SET @Ignore = @PageSize * ( @PageNum -1 ) SET ROWCOUNT @Ignore SELECT @LastID = CategoryID FROM dbo.Categories ORDER BY CategoryID DESC END ELSE BEGIN SET ROWCOUNT @PageSize END SET ROWCOUNT @PageSize SELECT * FROM dbo.Categories WHERE CategoryID < @LastID ORDER BY CategoryID DESC SET ROWCOUNT 0
(2)临时表或者表变量的自动增长列
ALTER PROCEDURE uspOrderDetailsList ( @Page INT = 1 ,@PageSize INT= 10 ) AS SET NOCOUNT ON -- create a memory-variable table to hold order ids DECLARE @TempTable TABLE (OrderID INT, ProductID INT, RowNum INT identity) -- insert the table ids and row numbers into the memory table INSERT INTO @TempTable ( OrderID , ProductID ) SELECT OrderID ,EmployeeID FROM dbo.Orders ORDER BY OrderID, EmployeeID -- return paging info SELECT ((@Page - 1) * @PageSize + 1) AS 'Start' ,@Page * @PageSize AS 'End' ,(SELECT COUNT(*) FROM @TempTable) AS 'Total' -- select only those rows belonging to the proper page SELECT d.OrderID ,d.EmployeeID FROM dbo.Orders d INNER JOIN @TempTable t ON d.OrderID = t.OrderID AND d.EmployeeID = t.ProductID WHERE t.RowNum BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
--------------------------------------------------------------------------------------------MemberShip中获取用户分页
CREATE PROCEDURE dbo.aspnet_Membership_GetAllUsers @ApplicationName nvarchar(256), @PageIndex int, @PageSize int AS BEGIN DECLARE @ApplicationId uniqueidentifier SELECT @ApplicationId = NULL SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName IF (@ApplicationId IS NULL) RETURN 0 -- Set the page bounds DECLARE @PageLowerBound int DECLARE @PageUpperBound int DECLARE @TotalRecords int SET @PageLowerBound = @PageSize * @PageIndex SET @PageUpperBound = @PageSize - 1 + @PageLowerBound -- Create a temp table TO store the select results CREATE TABLE #PageIndexForUsers ( IndexId int IDENTITY (0, 1) NOT NULL, UserId uniqueidentifier ) -- Insert into our temp table INSERT INTO #PageIndexForUsers (UserId) SELECT u.UserId FROM dbo.aspnet_Membership m, dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.UserId = m.UserId ORDER BY u.UserName SELECT @TotalRecords = @@ROWCOUNT SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate, u.UserId, m.IsLockedOut, m.LastLockoutDate FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound ORDER BY u.UserName RETURN @TotalRecords END GO