34,593
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('tempdb..#TB') IS NOT NULL
DROP TABLE #TB
GO
SELECT 20100729 DATE,'A' [TYPE],1000.0 [MONEY] INTO #TB UNION ALL
SELECT 20100729 ,'B' ,2000.0 UNION ALL
SELECT 20100728 ,'A' ,1100.0 UNION ALL
SELECT 20100728 ,'B' ,2100.0 UNION ALL
SELECT 20100727 ,'A' ,1200.0 UNION ALL
SELECT 20100727 ,'B' ,2200.0 UNION ALL
SELECT 20100726 ,'A' ,1300.0 UNION ALL
SELECT 20100726 ,'B' ,2300.0
GO
SELECT * FROM #TB
--结果,此结构是否可以简化?
SELECT m.[TYPE],m.[本月累计],d.[当日收入]
FROM (SELECT [TYPE],SUM(MONEY) [本月累计] FROM #TB
WHERE DATE BETWEEN 20100701 AND 20100729
GROUP BY TYPE) m
JOIN (SELECT [TYPE],SUM(MONEY) [当日收入]FROM #TB
WHERE DATE = 20100729
GROUP BY TYPE) d
ON m.type = d.type
IF OBJECT_ID('tempdb..#TB') IS NOT NULL
DROP TABLE #TB
GO
SELECT 20100729 DATE,'A' [TYPE],1000.0 [MONEY] INTO #TB UNION ALL
SELECT 20100729 ,'B' ,2000.0 UNION ALL
SELECT 20100728 ,'A' ,1100.0 UNION ALL
SELECT 20100728 ,'B' ,2100.0 UNION ALL
SELECT 20100727 ,'A' ,1200.0 UNION ALL
SELECT 20100727 ,'B' ,2200.0 UNION ALL
SELECT 20100726 ,'A' ,1300.0 UNION ALL
SELECT 20100726 ,'B' ,2300.0
GO
SELECT * FROM #TB
--结果,此结构是否可以简化?
SELECT m.[TYPE],m.[本月累计],d.[当日收入]
FROM (SELECT [TYPE],SUM(MONEY) [本月累计] FROM #TB
WHERE DATE BETWEEN 20100701 AND 20100729
GROUP BY TYPE) m
JOIN (SELECT [TYPE],SUM(MONEY) [当日收入]FROM #TB
WHERE DATE = 20100729
GROUP BY TYPE) d
ON m.type = d.type
SELECT TYPE
,[本月累计]=SUM(CASE WHEN DATE BETWEEN 20100701 AND 20100729 THEN MONEY ELSE 0 END)
,[当日收入]=SUM(CASE WHEN DATE = 20100729 THEN MONEY ELSE 0 END)
FROM #TB
GROUP BY TYPE
/*
DATE TYPE MONEY
----------- ---- ---------------------------------------
20100729 A 1000.0
20100729 B 2000.0
20100728 A 1100.0
20100728 B 2100.0
20100727 A 1200.0
20100727 B 2200.0
20100726 A 1300.0
20100726 B 2300.0
TYPE 本月累计 当日收入
---- --------------------------------------- ---------------------------------------
A 4600.0 1000.0
B 8600.0 2000.0
TYPE 本月累计 当日收入
---- --------------------------------------- ---------------------------------------
A 4600.0 1000.0
B 8600.0 2000.0
*/