SELECT子句用子查询统计各部门职员、经理数量时遇子查询返回多值错误

为什么你的SQL会报错？ #

那个Subquery returned more than 1 value错误的根源很明确：你的子查询没有和外部查询的部门编号关联起来。

看一下你写的子查询：

(SELECT COUNT(*) FROM EMP WHERE JOB = 'CLERK' GROUP BY DEPTNO)

这个子查询会返回所有部门的CLERK数量，是一个多行结果集（比如部门10有1个、部门20有2个这类）。但外部查询的每一行（每个部门）只能接受一个单一的值来填充NUMBER OF CLERKS列，数据库自然就懵了——它不知道该选子查询里的哪一行对应当前部门。

正确的实现方案 #

这里给你三种常用的解决方案，你可以根据自己的数据库类型和需求选择：

方案1：关联子查询（修复你的原始思路）

给子查询加上和外部查询的deptno关联条件，让每个子查询只返回当前部门的统计数：

SELECT 
    deptno AS 'DEPARTMENT NUMBER',
    -- 关联外部查询的deptno，只统计当前部门的CLERK
    (SELECT COUNT(*) FROM EMP e2 WHERE e2.JOB = 'CLERK' AND e2.deptno = e1.deptno) AS 'NUMBER OF CLERKS',
    -- 同理关联统计MANAGER
    (SELECT COUNT(*) FROM EMP e2 WHERE e2.JOB = 'MANAGER' AND e2.deptno = e1.deptno) AS 'NUMBER OF MANAGERS'
FROM EMP e1
GROUP BY deptno;

方案2：CASE WHEN + COUNT（更高效的写法）

这种方法只需要扫描一次表，性能比多次子查询更好，适合数据量较大的场景：

SELECT 
    deptno AS 'DEPARTMENT NUMBER',
    -- 匹配到CLERK就计数，否则忽略NULL
    COUNT(CASE WHEN JOB = 'CLERK' THEN 1 END) AS 'NUMBER OF CLERKS',
    -- 同理统计MANAGER
    COUNT(CASE WHEN JOB = 'MANAGER' THEN 1 END) AS 'NUMBER OF MANAGERS'
FROM EMP
GROUP BY deptno;

原理是CASE WHEN会给符合条件的行返回1，不符合的返回NULL，而COUNT()函数会自动忽略NULL值，刚好得到对应职位的数量。

方案3：PIVOT（适合支持该语法的数据库）

如果你的数据库支持PIVOT（比如SQL Server、Oracle），可以用这种更简洁的写法：

-- SQL Server示例
SELECT 
    deptno AS 'DEPARTMENT NUMBER',
    CLERK AS 'NUMBER OF CLERKS',
    MANAGER AS 'NUMBER OF MANAGERS'
FROM (
    SELECT deptno, JOB FROM EMP
) AS SourceTable
PIVOT (
    COUNT(JOB)
    FOR JOB IN (CLERK, MANAGER)
) AS PivotTable;

这种写法把行转列，直接按职位分组统计每个部门的数量。

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