SQL對記錄結果進行按日匯總并支持分頁，無記錄填充0值
來源：程序員人生 發布時間：2015-04-25 09:25:11 閱讀次數：3712次
數據庫中的格式以下，是個記錄明細表，要求對明細表進行按日匯總倒序輸出。
用下面的SQL語句可以進行按日匯總并且可以分頁輸出
DECLARE @Days TABLE
(
日期 DATETIME
)
DECLARE @StartDate datetime='2014-04-05 14:22:20'--匯總的開始日期
DECLARE @EndDate datetime='2015-04-08 15:21:33'--匯總的結束日期
DECLARE @start DATETIME
DECLARE @E2_UserName varchar(100)='CN000001'--用戶名
DECLARE @StartID INT=1--分頁使用的獲得記錄的開始ID
DECLARE @EndID INT =15--分頁使用的獲得記錄的結束ID,測試為15條數據
SET @start = CONVERT(VARCHAR, @StartDate, 23 )+' 00:00:00'
WHILE @start <= CONVERT(VARCHAR, @EndDate, 23 )+' 00:00:00'
BEGIN
INSERT INTO @Days VALUES(@start)
SET @start = DATEADD(DAY, 1, @start)
END;
WITH orderList AS (
SELECT ROW_NUMBER() OVER (ORDER BY 日期 DESC)AS Row,--這里規定了是按日期倒序排列
SUM(MD_TuiJianJiang) AS 'MD_TuiJianJiang',
SUM(MD_JianDianJiang) AS 'MD_JianDianJiang',
SUM(MD_RiFenHong) AS 'MD_RiFenHong',
SUM(MD_JiHuo) AS 'MD_JiHuo',
SUM(MD_TGZXJiang) AS 'MD_TGZXJiang',
SUM(MD_LingDaoJiang) AS 'MD_LingDaoJiang',
SUM(MD_ShiChangJiang) AS 'MD_ShiChangJiang',
SUM(MD_Help) AS 'MD_Help',
SUM(MD_Luckly) AS 'MD_Luckly',
SUM(MD_FanBuJiang) AS 'MD_FanBuJiang',
SUM(MD_Month) AS 'MD_Month',
SUM(MD_CheckIN) AS 'MD_CheckIN',
SUM(TOTAL) AS 'TOTAL',
日期
FROM (
SELECT
SUM(ISNULL([MD_TuiJianJiang],0)) AS 'MD_TuiJianJiang',
SUM(ISNULL([MD_JianDianJiang],0)) AS 'MD_JianDianJiang',
SUM(ISNULL([MD_RiFenHong],0)) AS 'MD_RiFenHong',
SUM(ISNULL([MD_JiHuo],0)) AS 'MD_JiHuo',
SUM(ISNULL([MD_TGZXJiang],0)) AS 'MD_TGZXJiang',
SUM(ISNULL([MD_LingDaoJiang],0)) AS 'MD_LingDaoJiang',
SUM(ISNULL([MD_ShiChangJiang],0)) AS 'MD_ShiChangJiang',
SUM(ISNULL([MD_Help],0)) AS 'MD_Help',
SUM(ISNULL([MD_Luckly],0)) AS 'MD_Luckly',
SUM(ISNULL([MD_FanBuJiang],0)) AS 'MD_FanBuJiang',
SUM(ISNULL([MD_Month],0)) AS 'MD_Month',
SUM(ISNULL([MD_CheckIN],0)) AS 'MD_CheckIN',
SUM(ISNULL(MD_TuiJianJiang+MD_RiFenHong+MD_JianDianJiang+MD_JiHuo,0)) AS 'TOTAL',
--ISNULL(DAY(MD_Datetime),DAY(B.日期)) AS 'MD_Datetime',
ISNULL([E2_UserName],@E2_UserName) AS 'E2_UserName',
B.日期
FROM [E2_MoneyDetails]
RIGHT JOIN @Days B ON CONVERT(VARCHAR, MD_Datetime, 23 )+' 00:00:00'=B.日期 AND [E2_UserName]=@E2_UserName
WHERE B.日期 BETWEEN DATEADD(DAY, ⑴, @StartDate) AND @EndDate
GROUP BY B.日期,E2_UserName
) E
GROUP BY 日期
)
SELECT *
FROM orderlist
WHERE Row BETWEEN @StartID AND @EndID
終究的結果樣式為
生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
