查询各公司唯一专家员工的亲属数量SQL语句修正求助

需求核心 #

需要统计的是各公司中，身为所在公司该专业唯一持有者的员工的亲属数量，核心包含两个条件：

• 员工所属公司内，该员工的专业（expertise_id）仅对应自己一人
• 统计这类员工的亲属总数，同时关联公司信息

原查询的问题 #

原SQL仅实现了“统计员工亲属数量”的基础功能，完全未处理“公司内唯一专家”的筛选逻辑，也没有关联公司数据，无法满足需求。

修正后的SQL方案 #

方案一：使用子查询筛选唯一专家 #

SELECT 
    c.id AS company_id,
    c.name AS company_name,
    e.id AS employee_id,
    e.name AS employee_name,
    COUNT(dr.id) AS qty_dependantrelatives
FROM company c
JOIN employee e ON c.id = e.company_id
LEFT JOIN dependantrelative dr ON e.id = dr.employee_id
-- 筛选公司内该专业仅当前员工一人的记录
WHERE EXISTS (
    SELECT 1
    FROM employee e2
    WHERE e2.company_id = e.company_id
      AND e2.expertise_id = e.expertise_id
    GROUP BY e2.company_id, e2.expertise_id
    HAVING COUNT(e2.id) = 1
)
GROUP BY c.id, c.name, e.id, e.name
ORDER BY c.id, e.id;

方案二：使用窗口函数（更简洁） #

WITH unique_expert_employees AS (
    SELECT 
        e.id AS employee_id,
        e.name AS employee_name,
        c.id AS company_id,
        c.name AS company_name,
        -- 计算同公司同专业的员工总数
        COUNT(*) OVER(PARTITION BY e.company_id, e.expertise_id) AS same_expertise_count
    FROM employee e
    JOIN company c ON e.company_id = c.id
)
SELECT 
    u.company_id,
    u.company_name,
    u.employee_id,
    u.employee_name,
    COUNT(dr.id) AS qty_dependantrelatives
FROM unique_expert_employees u
LEFT JOIN dependantrelative dr ON u.employee_id = dr.employee_id
WHERE u.same_expertise_count = 1
GROUP BY u.company_id, u.company_name, u.employee_id, u.employee_name
ORDER BY u.company_id, u.employee_id;

关键逻辑说明 #

1. 唯一专家筛选：
   • 子查询版本：通过关联同公司同专业的员工，分组后筛选出员工数为1的专业-公司组合
   • 窗口函数版本：用PARTITION BY按公司+专业分组，计算每组员工数，直接筛选数量为1的员工
2. 亲属统计：使用LEFT JOIN关联亲属表，避免遗漏无亲属的符合条件员工（若不需要无亲属的员工，可改为INNER JOIN）
3. 分组聚合：按公司和员工的唯一标识分组，确保每个员工的亲属数被正确统计

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