"Adding" 在一个查询中分组依据和排序依据，而不是单独的查询

Question

我有一个基本查询，它有一些变体，所有这些变体在GROUP BY和ORDER BY子句中都不同。

这些是变体：

SELECT SUM(col_a) AS "col_a", SUM(col_b), AS "col_b", SUM(col_c) as "col_c"
FROM my_table
GROUP BY col_a
SELECT SUM(col_a) AS "col_a", SUM(col_b), AS "col_b", SUM(col_c) as "col_c"
FROM my_table
GROUP BY col_a, col_b
ORDER BY col_a
SELECT SUM(col_a) AS "col_a", SUM(col_b), AS "col_b", SUM(col_c) as "col_c"
FROM my_table
GROUP BY col_a, col_b, col_c
ORDER BY col_a, col_b

可以在同一个查询中执行吗？或者我应该获取基本数据并对服务器端/客户端进行汇总？

Answer 1

在MySQL中(这是最初的问题(，您可以使用WITH ROLLUP:在同一查询中完成

select sum(col_a) as col_a, sum(col_b) as col_b, sum(col_c) as col_c, count(*),
case (col_a is not null) + (col_b is not null) + (col_c is not null) when 3 then 'col_a,col_b,col_c' when 2 then 'col_a,col_b' when 1 then 'col_a' end grouped_by
from my_table
group by col_a, col_b, col_c with rollup
having grouped_by is not null
order by grouped_by,col_a, col_b, col_c

小提琴

Answer 2

是的，可以使用GROUPING SETS或ROLLUP。前者更灵活，虽然有点冗长。

SELECT
SUM(col_a) AS col_a,
SUM(col_b) AS col_b,
SUM(col_c) as col_c
FROM my_table
GROUP BY GROUPING SETS (
(col_a),
(col_a, col_b),
(col_a, col_b, col_c)
)
ORDER BY col_a, col_b, col_c;

要识别汇总的行，可以执行类似的操作

SELECT
CASE WHEN GROUPING(col_b) = 0 AND GROUPING(col_c) = 0
THEN 'By A, B, C'
WHEN GROUPING(col_b) = 0
THEN 'By A, B'
ELSE   'By A'
END AS GroupingType,
SUM(col_a) AS col_a,
SUM(col_b) AS col_b,
SUM(col_c) as col_c
FROM my_table
GROUP BY GROUPING SETS (
(col_a),
(col_a, col_b),
(col_a, col_b, col_c)
)
ORDER BY col_a, col_b, col_c;