统计分页一些sql

USE [QPTreasureDB]
GO
/****** Object: StoredProcedure [dbo].[GameStatistics] Script Date: 2018/8/16 10:33:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[GameStatistics]
@Type INT,
@GameType INT,
@Year INT,
@Month INT,
@Day INT,
@PageIndex INT,
@PageSize INT
AS -- 属性设置
SET NOCOUNT ON;

-- 执行逻辑
BEGIN
IF (@Type = 0)
BEGIN
IF (@GameType = 2)
BEGIN
SELECT SUM(-lwinscore) AS TotalProfitAndLoss, --总盈亏
SUM(lalladdscore) AS PlayIn, --玩入
SUM(- (lwinscore + lalladdscore)) AS PlayOut --玩出
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = CASE @Year
WHEN -1 THEN YEAR(insertTime)
ELSE @Year END
AND MONTH(insertTime) = CASE @Month
WHEN -1 THEN MONTH(insertTime)
ELSE @Month END
AND DAY(insertTime) = CASE @Day
WHEN -1 THEN DAY(insertTime)
ELSE @Day END;
END;
IF (@GameType = 3)
BEGIN
SELECT YEAR(insertTime) Year,
SUM(-lwinscore) AS RichTotalProfitAndLoss, --总盈亏
SUM(lalladdscore) AS RichPlayIn, --玩入
SUM(- (lwinscore + lalladdscore)) AS RichPlayOut --玩出
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
GROUP BY YEAR(insertTime);
END;
END;
ELSE IF (@Type = 1) --月统计
BEGIN
SELECT @Year Year,
MONTH(insertTime) Month,
SUM(-lwinscore) AS RichTotalProfitAndLoss, --总盈亏
SUM(lalladdscore) AS RichPlayIn, --玩入
SUM(- (lwinscore + lalladdscore)) AS RichPlayOut --玩出
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = @Year
GROUP BY MONTH(insertTime);
END;
ELSE IF (@Type = 2) --日统计
BEGIN
SELECT @Year Year,
@Month Month,
DAY(insertTime) Day,
SUM(-lwinscore) AS RichTotalProfitAndLoss, --总盈亏
SUM(lalladdscore) AS RichPlayIn, --玩入
SUM(- (lwinscore + lalladdscore)) AS RichPlayOut --玩出
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = @Year
AND MONTH(insertTime) = @Month
GROUP BY DAY(insertTime);
END;
ELSE IF (@Type = 3) --日统计
BEGIN
DECLARE @RecordCount INT;
SELECT @RecordCount = COUNT(*)
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = @Year
AND MONTH(insertTime) = @Month
AND DAY(insertTime) = @Day
GROUP BY dwUserID,
lwinscore,
lalladdscore,
lAreaScore1,
lAreaScore2,
lAreaScore3,
lAreaScore4,
lAreaScore5,
insertTime;
DECLARE @TotalProfitAndLoss VARCHAR(255)
DECLARE @TotalPlayIn VARCHAR(255)
DECLARE @TotalPlayOut VARCHAR(255)
DECLARE @PageCount INT

SELECT 
@PageCount=COUNT(dwUserID),
@TotalProfitAndLoss=SUM(-lwinscore), --总盈亏
@TotalPlayIn=SUM(lalladdscore), --玩入
@TotalPlayOut=SUM(- (lwinscore + lalladdscore)) --玩出
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = @Year
AND MONTH(insertTime) = @Month
AND DAY(insertTime) = @Day;
DECLARE @StartIndex INT ,
@EndIndex INT
SELECT @StartIndex = ( @PageIndex - 1 ) * @PageSize + 1 ,
@EndIndex = @PageIndex * @PageSize;
WITH Pager
AS ( SELECT ROW_NUMBER() OVER (ORDER BY dwUserID ASC) AS Id,
dwUserID,
@PageCount PageCount,
@TotalProfitAndLoss AS TotalProfitAndLoss, --总盈亏
@TotalPlayIn AS TotalPlayIn, --玩入
@TotalPlayOut AS TotalPlayOut, --玩出
-lwinscore AS RichProfitAndLoss, --总盈亏
lalladdscore AS RichPlayIn, --玩入
- (lwinscore + lalladdscore) AS RichPlayOut, --玩出
lAreaScore1,
lAreaScore2,
lAreaScore3,
lAreaScore4,
lAreaScore5,
insertTime
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = @Year
AND MONTH(insertTime) = @Month
AND DAY(insertTime) = @Day
GROUP BY dwUserID,
lwinscore,
lalladdscore,
lAreaScore1,
lAreaScore2,
lAreaScore3,
lAreaScore4,
lAreaScore5,
insertTime)
SELECT *
FROM Pager
WHERE Id BETWEEN @StartIndex AND @EndIndex

END;
END;

 

转载于:https://www.cnblogs.com/RambleLife/p/9485848.html