数据库分页

 

USE TestDB
GO

IF OBJECTPROPERTY(OBJECT_ID(N'getAdminOperationLog'), N'IsProcedure') = 1
　　DROP PROCEDURE dbo.getAdminOperationLog
GO


--获取后台日志
CREATE PROCEDURE getAdminOperationLog
(
    @companyId INT,
    @startDate DATETIME,
    @endDate   DATETIME,

    @pageIndex INT,
    @pageSize  INT,
    @total     INT OUTPUT
)
AS 
SET NOCOUNT ON


IF EXISTS  (SELECT 1 FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'tempdb..#tempLog') AND type='U')
   DROP TABLE #tempLog

SELECT FID,FCompanyId,FOperatorId,FCreateTime,FIp,FModuleName,FOperatorContent
INTO #tempLog
FROM dbo.TAdminOperationLog WITH(NOLOCK)
WHERE FCompanyId=@companyId
AND FCreateTime BETWEEN @startDate AND @endDate


--总条数
SELECT @total = COUNT(1) FROM #tempLog

--分页
DECLARE @skip INT = (@pageIndex-1) * @pageSize
SELECT * FROM #tempLog
ORDER BY FID DESC
OFFSET @skip ROW FETCH NEXT @pageSize ROWS ONLY;



--清理临时表
IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..#tempLog') AND type='U')
BEGIN 
    TRUNCATE TABLE #tempLog
    DROP TABLE #tempLog
END 

SET NOCOUNT OFF

--2012分页示例

SELECT *
INTO #tempLog2012
FROM dbo.TAdminOperationLog WITH(NOLOCK)
WHERE FCompanyId=36052


--总条数
DECLARE @total INT
SELECT @total = COUNT(*) FROM #tempLog2012


DECLARE @pageIndex INT = 2
DECLARE @pageSize  INT = 20


--2012分页
DECLARE @skip INT = (@pageIndex-1) * @pageSize
SELECT * FROM #tempLog2012
ORDER BY FID DESC
OFFSET @skip ROW FETCH NEXT @pageSize ROWS ONLY;

SELECT @total

 

--2008分页示例

SELECT ROW_NUMBER() OVER(ORDER BY FID DESC) AS 'FIndex',*
INTO #tempLog2008
FROM dbo.TAdminOperationLog WITH(NOLOCK)
WHERE FCompanyId=36052


--总条数
DECLARE @total INT
SELECT @total = COUNT(*) FROM #tempLog2008


DECLARE @pageIndex INT = 2
DECLARE @pageSize  INT = 20

DECLARE @skip INT = (((@pageIndex-1)*@pageSize)+1)
DECLARE @take INT = (@pageIndex * @pageSize)


SELECT * FROM #tempLog2008
WHERE FIndex BETWEEN @skip AND @take

SELECT @total