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

很好的sql 文档,可能邦你成为sql 高手

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
--*/