Oracle SQL实现经理下属统计及EMPID同行展示的查询方案

现有表结构与数据 #

Oracle数据库中有EMPLOYEE表，数据如下：

其中EMPID是唯一列，manager字段存储对应经理的EMPID。

目标查询结果 #

需要得到如下格式的结果（以EM2为例，统计其下属并转置为列）：

当前已实现的查询语句 #

select 
  e2.empid as manager,
  e2.name manger_name,
  count(*) over (partition by e2.empid) as employee_count,
  e1.empid as employee,
  e1.name as employee_name
from employee e1 left join employee e2 on e1.manager = e2.empid

当前查询结果 #

执行上述语句后得到的结果：

解决方案 #

方案1：固定列数的PIVOT（适用于已知最多下属数量的场景） #

WITH ranked_employees AS (
    SELECT 
        manager,
        empid,
        ROW_NUMBER() OVER (PARTITION BY manager ORDER BY empid) AS emp_rank
    FROM employee
    WHERE manager IS NOT NULL
)
SELECT 
    manager AS EMPID,
    COUNT(*) AS COUNT,
    "1" AS EMP1,
    "2" AS EMP2
FROM ranked_employees
PIVOT (
    MAX(empid) FOR emp_rank IN (1, 2)
)
WHERE manager = 'EM2'; -- 若需要所有经理的结果，可删除此条件

方案2：动态列数（下属数量不固定时使用） #

如果需要根据实际下属数量自动生成对应EMP列，可使用动态SQL：

DECLARE
    cols VARCHAR2(1000);
    sql_stmt VARCHAR2(2000);
BEGIN
    -- 生成动态列名
    SELECT LISTAGG('''' || rn || ''' AS EMP' || rn, ', ') WITHIN GROUP (ORDER BY rn)
    INTO cols
    FROM (
        SELECT DISTINCT ROW_NUMBER() OVER (PARTITION BY manager ORDER BY empid) AS rn
        FROM employee
        WHERE manager IS NOT NULL
    );

    -- 构建并执行动态SQL
    sql_stmt := '
        WITH ranked_employees AS (
            SELECT 
                manager,
                empid,
                ROW_NUMBER() OVER (PARTITION BY manager ORDER BY empid) AS emp_rank
            FROM employee
            WHERE manager IS NOT NULL
        )
        SELECT 
            manager AS EMPID,
            COUNT(*) AS COUNT,
            ' || cols || '
        FROM ranked_employees
        PIVOT (
            MAX(empid) FOR emp_rank IN (' || REPLACE(cols, ' AS EMP', '') || ')
        )
        ORDER BY manager';

    EXECUTE IMMEDIATE sql_stmt;
END;
/

说明 #

• 方案1适合下属数量固定的场景，直接指定转置列数即可；
• 方案2通过动态SQL自动适配下属数量，灵活性更强；
• 若仅需特定经理的统计结果，在方案1中添加WHERE manager = '目标EMPID'即可。

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