一套sql题有人会的吗？

img

此代码适用于MS SQL Server

SELECT
d.dept_id AS '部门编号',
d.dept_name AS '部门名称',
(SELECT COUNT(*) FROM employee WHERE  dept_id=d.dept_id AND emp_wage BETWEEN 0 AND 4999) AS '5K以下员工人数',
(SELECT SUM(emp_wage) FROM employee WHERE  dept_id=d.dept_id AND BETWEEN 0 AND 4999) AS '5K以下薪酬小计',
(SELECT COUNT(*) FROM employee WHERE  dept_id=d.dept_id AND emp_wage BETWEEN 5000 AND 10000) AS '5K-10K员工人数',
(SELECT SUM(emp_wage) FROM employee WHERE  dept_id=d.dept_id AND emp_wage BETWEEN 5000 AND 10000) AS '5K-10K薪酬小计',
(SELECT COUNT(*) FROM employee WHERE  dept_id=d.dept_id AND emp_wage>10000) AS '10K以上员工人数',
(SELECT SUM(emp_wage) FROM employee WHERE  dept_id=d.dept_id AND emp_wage >10000) AS '10K以上薪酬小计',
(SELECT COUNT(*) FROM employee WHERE  dept_id=d.dept_id) AS '部门员工人数总数',
(SELECT SUM(emp_wage) FROM employee WHERE  dept_id=d.dept_id) AS '部门薪酬合计',
(SELECT AVG(emp_wage) FROM employee WHERE  dept_id=d.dept_id) AS '部门薪酬均值',
(SELECT MAX(emp_wage) FROM employee WHERE  dept_id=d.dept_id) AS '部门薪酬最大值',
(SELECT MIN(emp_wage) FROM employee WHERE  dept_id=d.dept_id) AS '部门薪酬最小值'
FROM department AS d ORDER BY d.dept_id ASC

这个题不难，希望只提供思路，不提供具体的sql：
部门表 left jion 员工表 ，以dept_id 做为关联字段
统计的输出字段，通过 count(case when ...) 与 sum(case when ...) ，还有avg(case when ...) 三个便可完成统计。

注意关联出来的null值处理

您好，我是有问必答小助手，您的问题已经有小伙伴帮您解答，感谢您对有问必答的支持与关注！
PS：问答VIP年卡 【限时加赠：IT技术图书免费领】，了解详情>>> https://vip.csdn.net/askvip?utm_source=1146287632