6.3.1 简单的交叉报表处理示例.sql

来自「sqlserver 数据库编程的绝好脚本」· SQL 代码 · 共 26 行

DECLARE @t TABLE(Year int,Quarter int,Amount decimal(10,1))
INSERT @t SELECT 1990, 1, 1.1
UNION ALL SELECT 1990, 2, 1.2
UNION ALL SELECT 1990, 3, 1.3
UNION ALL SELECT 1990, 4, 1.4
UNION ALL SELECT 1991, 1, 2.1
UNION ALL SELECT 1991, 2, 2.2
UNION ALL SELECT 1991, 3, 2.3
UNION ALL SELECT 1991, 4, 2.4

--查询处理
SELECT Year,
	Q1=SUM(CASE Quarter WHEN 1 THEN Amount END),
	Q2=SUM(CASE Quarter WHEN 2 THEN Amount END),
	Q3=SUM(CASE Quarter WHEN 3 THEN Amount END),
	Q4=SUM(CASE Quarter WHEN 4 THEN Amount END)
FROM @t
GROUP BY Year

/*--结果
Year      Q1     Q2     Q3     Q4
--------- ------------------------------
1990      1.1    1.2    1.3    1.4
1991      2.1    2.2    2.3    2.4
--*/