如何在统计各部门员工人数的SQL语句中使用ROLLUP功能

你可以根据你使用的数据库选择对应写法修改原SQL：

• 适用于MySQL的WITH ROLLUP写法

select count(*) as count, d.department_name 
from employees e inner join departments d on e.department_id = d.department_id 
group by d.department_name WITH ROLLUP;

• 适用于Oracle、PostgreSQL 10+、SQL Server的标准ROLLUP函数写法

select count(*) as count, d.department_name 
from employees e inner join departments d on e.department_id = d.department_id 
group by ROLLUP(d.department_name);

以上两种写法执行后都会在原有部门统计结果的最后新增一条汇总行，该行department_name字段值为NULL，count字段值为所有部门的员工总人数。

如果你想把汇总行的NULL替换为更直观的总计字样，可以使用COALESCE函数做判断，示例如下：

select count(*) as count, COALESCE(d.department_name, '总计') as department_name 
from employees e inner join departments d on e.department_id = d.department_id 
group by ROLLUP(d.department_name);
-- 如果是MySQL环境，只需要把group by语句替换为`group by d.department_name WITH ROLLUP`即可

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