多列嵌套计数问题：基于Employee与Machine_Control表统计机器及位置数

现有数据表 #

Employee表 #

EMPID License  Experience  Salary
----  ------  ----------  ------
ABC1  3256     5 years     $1000
ABC2  1324     10 years    $3000
ABC3  2345     11 years    $2500

Machine_Control表 #

MID   MCODE OPRID   SMID   SMCODE  MLOCATION
-------------------------------------------
M1     1     ABC1      NULL    NULL   LOCATION1
M1     2     ABC2      NULL    NULL   LOCATION2
M1     3     NULL      M1      1      LOCATION1
M1     4     ABC1      NULL    NULL   LOCATION3
M1     5     NULL      M1      2      LOCATION2

统计需求 #

需要统计每个EMPID对应的No_Machines（关联的机器总数，需同时统计主键(MID,MCODE)和副键(SMID,SMCODE)关联的记录）和No_Locations（关联的唯一位置数量），期望输出如下：

EMPID  License Experience No_Machines  No_Locations
--------------------------------------------------
ABC1    3256      5 years     3              2
ABC2    1324      10 years    2              1
ABC3    2345      11 years    0              0

尝试的SQL及错误结果 #

使用以下SQL语句：

select a.EMPID, a.Licesne, a.Experience, count(b.MID) No_Machines, count(distinct b.MLOCATION) No_Locations
from Employee a
    left join Machine_Control b on a.EMPID= b.OPRDID
group by a.EMPID;

得到不符合预期的结果：

EMPID  License Experience No_Machines  No_Locations
--------------------------------------------------
ABC1    3256      5 years     2              2
ABC2    1324      10 years    1              1
ABC3    2345      11 years    0              0

修正后的SQL语句 #

SELECT 
    e.EMPID,
    e.License,
    e.Experience,
    COUNT(mc.MID) AS No_Machines,
    COUNT(DISTINCT mc.MLOCATION) AS No_Locations
FROM Employee e
LEFT JOIN (
    -- 直接关联OPRID的机器记录
    SELECT MID, MCODE, OPRID, MLOCATION
    FROM Machine_Control
    WHERE OPRID IS NOT NULL
    UNION ALL
    -- 通过副键关联到主机器，映射到对应员工的记录
    SELECT mc.MID, mc.MCODE, main.OPRID, mc.MLOCATION
    FROM Machine_Control mc
    JOIN Machine_Control main 
        ON mc.SMID = main.MID AND mc.SMCODE = main.MCODE
    WHERE mc.OPRID IS NULL
) mc ON e.EMPID = mc.OPRID
GROUP BY e.EMPID, e.License, e.Experience;

说明 #

1. 子查询通过UNION ALL合并两类有效记录：
   • 第一部分是Machine_Control中OPRID非空的直接关联记录
   • 第二部分是OPRID为空的记录，通过SMID/SMCODE关联到主机器，获取主机器对应的OPRID完成映射
2. 用LEFT JOIN保证所有员工都被统计，包括无关联机器的ABC3
3. COUNT(mc.MID)统计合并后的所有机器记录数，COUNT(DISTINCT mc.MLOCATION)统计唯一的位置数量

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