我们将用下图研究报表:
1、简单报表操作:
上表经过下列运算后可产生:
SELECT [Year], Q1=SUM(CASE WHEN [Quarter]=1 THEN Quantity END), Q2=SUM(CASE WHEN [Quarter]=2 THEN Quantity END), Q3=SUM(CASE WHEN [Quarter]=3 THEN Quantity END), Q4=SUM(CASE WHEN [Quarter]=4 THEN Quantity END) FROM tb GROUP BY [Year]
补充:除了SUM外,也可以用其他常用聚合函数例如AVG、MIN、SUM、STDEV、COUNT、STDEVP、VAR、GROUPING 、VARP、MAX
再次补充:
STDEV 返回给定表达式中所有值的统计标准偏差。
STDEVP 返回给定表达式中所有值的填充统计标准偏差。
VAR 返回给定表达式中所有值的统计方差。
VARP 返回给定表达式中所有值的填充统计方差。
注意:以上聚合函数 都只能用于数字列
GROUPING 常和CUBE和ROLLUP结合使用 如果是附加列输出1否则输出0
2、涉及多列的交叉报表
SQL语句:
SELECT [Year], Q1_Quantity=SUM(CASE WHEN [Quarter]=1 THEN Quantity END), Q1_Price=CAST(AVG(CASE WHEN [Quarter]=1 THEN Quantity END) AS DECIMAL(10,2)), Q1_Money=SUM(CASE WHEN [Quarter]=1 THEN CAST(Quantity*Price AS DECIMAL(10,2)) END), Q2_Quantity=SUM(CASE WHEN [Quarter]=2 THEN Quantity END), Q2_Price=CAST(AVG(CASE WHEN [Quarter]=2 THEN Quantity END) AS DECIMAL(10,2)), Q2_Money=SUM(CASE WHEN [Quarter]=2 THEN CAST(Quantity*Price AS DECIMAL(10,2)) END), Q3_Quantity=SUM(CASE WHEN [Quarter]=3 THEN Quantity END), Q3_Price=CAST(AVG(CASE WHEN [Quarter]=3 THEN Quantity END) AS DECIMAL(10,2)), Q3_Money=SUM(CASE WHEN [Quarter]=3 THEN CAST(Quantity*Price AS DECIMAL(10,2)) END), Q4_Quantity=SUM(CASE WHEN [Quarter]=4 THEN Quantity END), Q4_Price=CAST(AVG(CASE WHEN [Quarter]=4 THEN Quantity END) AS DECIMAL(10,2)), Q4_Money=SUM(CASE WHEN [Quarter]=4 THEN CAST(Quantity*Price AS DECIMAL(10,2)) END) FROM tb GRO<span style="color: #ff0000">UP BY [Year] </span>
3、动态生成交叉报表的列:
这次生成的图表如上图 SQL可写作: