sql - "Adding"GROUP BY 's and ORDER BY' 在一个查询中而不是单独的

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

fiddle