group by grouping sets 的用法
先看group by:SELECT A FROM T GROUP BY A;
对
//返回的结果就是按照字段A分,列出这几种
SELECT A,B FROM T GROUP BY A;
错
SELECT A,SUM (B) FROM T GROUP BY A;
对
这就是说,在select指定的字段要么就要包含在Group By语句的后面,作为分组的依据;要么就要被包含在聚合函数中。举例说明:前面有B,后面就必须有B,如果前面聚合函数(B),则后面可以不出现B
WHERE 后面不能接聚合函数,如果要接,就用HAVING
grouping sets:相当于将 a.type 和 null分别作为分组条件进行查询,然后将两条查询结果union all想合计谁,就SUM()它
SELECT (case
when a.type is null then
'合计'
else
a. type
end) as type,
sum(a.cnt)
FROM test a
group by grouping sets(a. type,());
它相当于:
SELECT a.type, sum(a.cnt)
FROM test a
group by a.type
union all (SELECT '合计', sum(a.cnt) FROM TEST a);