如何在SQL中添加分组名称及实现部门小计前置？

要在员工分组查询结果前显示对应部门的总计行，你可以通过以下两种方式实现：

方法一：UNION ALL合并两组查询 #

通过分别统计部门总计和员工明细，再合并结果并排序，确保部门总计排在对应部门的员工数据前：

-- 统计部门总计
SELECT 
    department,
    '部门总计' AS EmpName,
    SUM(Debi) AS SumDebi,
    SUM(Credi) AS SumCredi
FROM Total_Income
GROUP BY department

UNION ALL

-- 统计员工明细分组
SELECT 
    department,
    EmpName,
    SUM(Debi) AS SumDebi,
    SUM(Credi) AS SumCredi
FROM Total_Income
GROUP BY department, EmpName

-- 排序：先按部门分组，再让部门总计行排在最前
ORDER BY department, EmpName = '部门总计' DESC;

方法二：使用ROLLUP简化查询（支持的数据库：MySQL、SQL Server等） #

利用WITH ROLLUP自动生成分组总计行，再通过条件过滤和排序调整展示顺序：

SELECT 
    department,
    -- 判断是否为部门总计行，替换显示文本
    CASE 
        WHEN GROUPING(EmpName) = 1 THEN '部门总计'
        ELSE EmpName
    END AS EmpName,
    SUM(Debi) AS SumDebi,
    SUM(Credi) AS SumCredi
FROM Total_Income
GROUP BY department, EmpName WITH ROLLUP
-- 排除整个表的总计行，只保留部门和员工级别的数据
HAVING GROUPING(department) = 0
-- 确保部门总计行在对应部门员工数据前
ORDER BY department, GROUPING(EmpName) DESC;

两种方法都能实现你要的效果：每个部门的总计行排在该部门所有员工分组数据的最前面，之后是该部门下各员工的统计结果。

内容的提问来源于stack exchange，提问作者Johan