如何使用Oracle SQL的REFERENCES统计Danny下属John监管的员工数量

要实现上下级从属关系的统计，首先需要通过REFERENCES关键字建立自引用外键，保证员工的上级ID关联到合法的员工记录，表结构示例如下：

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50) NOT NULL,
    manager_id NUMBER,
    -- 自引用外键：manager_id必须关联已存在的员工emp_id
    CONSTRAINT fk_emp_manager FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

分两种场景提供实现方案：

场景1：统计John管辖的所有员工（含直接、间接下属） #

使用Oracle原生层级查询语法CONNECT BY实现，同时会校验John确实属于Danny的监管范围：

SELECT COUNT(*) AS john_subordinate_count
FROM employees
START WITH emp_name = 'John'
-- 校验John是Danny的下属
AND EXISTS (
    SELECT 1
    FROM employees
    START WITH emp_name = 'John'
    CONNECT BY PRIOR manager_id = emp_id
    CONNECT_BY_ROOT emp_name = 'Danny'
)
-- 遍历John的所有下属，排除John本人
CONNECT BY PRIOR emp_id = manager_id
AND PRIOR emp_name <> 'John';

场景2：仅统计John的直接下属 #

不需要遍历层级，直接关联查询即可：

SELECT COUNT(*) AS john_direct_subordinate_count
FROM employees e
INNER JOIN employees john ON e.manager_id = john.emp_id
INNER JOIN employees danny ON john.manager_id = danny.emp_id
WHERE john.emp_name = 'John'
AND danny.emp_name = 'Danny';

注意：如果表中存在同名员工，建议使用唯一的emp_id替代emp_name作为筛选条件，避免统计结果偏差。

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