合并多条分组查询SQL语句的方法

当需要从同一表中获取不同分组条件的统计结果时，可以通过以下几种方式将多条SQL合并为一条：

方法1：使用GROUPING SETS

SELECT 
    department_id,
    job_id,
    COUNT(*) as emp_count,
    SUM(salary) as total_salary
FROM employees
GROUP BY GROUPING SETS (
    (department_id),
    (job_id),
    ()
);

这会返回三个结果集的并集： 1. 按department_id分组的结果 2. 按job_id分组的结果 3. 不分组的总计结果

方法2：使用CUBE

SELECT 
    department_id,
    job_id,
    COUNT(*) as emp_count,
    SUM(salary) as total_salary
FROM employees
GROUP BY CUBE (department_id, job_id);

CUBE会生成所有可能的分组组合，包括： - 按department_id分组 - 按job_id分组 - 按department_id和job_id组合分组 - 不分组的总计

方法3：使用ROLLUP

SELECT 
    department_id,
    job_id,
    COUNT(*) as emp_count,
    SUM(salary) as total_salary
FROM employees
GROUP BY ROLLUP (department_id, job_id);

ROLLUP生成层次化的分组结果，从最详细到总计。

方法4：使用UNION ALL组合多个查询

SELECT 'department' as group_type, department_id as group_id, 
       COUNT(*) as emp_count, SUM(salary) as total_salary
FROM employees
GROUP BY department_id

UNION ALL

SELECT 'job' as group_type, job_id as group_id,
       COUNT(*) as emp_count, SUM(salary) as total_salary
FROM employees
GROUP BY job_id

UNION ALL

SELECT 'total' as group_type, NULL as group_id,
       COUNT(*) as emp_count, SUM(salary) as total_salary
FROM employees;

注意事项

1. GROUPING SETS、CUBE和ROLLUP是SQL标准功能，但不同数据库实现可能略有差异
2. 对于大数据量，CUBE可能生成过多组合，需谨慎使用
3. 可以配合GROUPING()函数识别哪些列参与了当前行的分组

选择哪种方法取决于具体需求、数据库支持情况和性能考虑。