一套sql题有人会的吗？

Question

Answer 1

此代码适用于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