PostgreSQL单查询实现多外键关联表替换与统计需求

以下是满足你需求的单条SQL查询，可直接替代三次单独查询的方式：

SELECT
    d.department_name,
    -- 替换外键为对应人员姓氏
    sup.last_name AS supervisor_last_name,
    sec.last_name AS secretary_last_name,
    ra.last_name AS research_assistant_last_name,
    -- 获取三类人员的薪资与工龄
    sup.salary AS supervisor_salary,
    sec.salary AS secretary_salary,
    ra.salary AS research_assistant_salary,
    sup.tenure AS supervisor_tenure,
    sec.tenure AS secretary_tenure,
    ra.tenure AS research_assistant_tenure,
    -- 计算薪资总和与工龄累计值（用COALESCE处理空值，避免岗位无人时统计异常）
    COALESCE(sup.salary, 0) + COALESCE(sec.salary, 0) + COALESCE(ra.salary, 0) AS total_salary,
    COALESCE(sup.tenure, 0) + COALESCE(sec.tenure, 0) + COALESCE(ra.tenure, 0) AS total_tenure
FROM
    Statistics_Table st
-- 关联People_Table获取主管信息
LEFT JOIN People_Table sup ON st.supervisor_id = sup.person_id
-- 关联People_Table获取秘书信息
LEFT JOIN People_Table sec ON st.secretary_id = sec.person_id
-- 关联People_Table获取助理信息
LEFT JOIN People_Table ra ON st.research_assistant = ra.person_id
-- 关联部门表替换department_id为名称（假设部门表名为Department_Table，字段为department_id、department_name）
LEFT JOIN Department_Table d ON st.department_id = d.department_id
-- 按部门分组
GROUP BY
    d.department_name,
    sup.last_name, sup.salary, sup.tenure,
    sec.last_name, sec.salary, sec.tenure,
    ra.last_name, ra.salary, ra.tenure
-- 按薪资总和降序排序，取前20条
ORDER BY
    total_salary DESC
LIMIT 20;

关键逻辑说明： #

• 多表关联：通过三次LEFT JOIN关联People_Table，分别映射三类岗位的外键到对应的人员姓氏、薪资和工龄，使用LEFT JOIN确保即使某岗位无人员，这条记录也能被保留。
• 空值处理：用COALESCE将空薪资/工龄转为0，避免聚合计算时出现NULL结果。
• 分组与排序：按部门名称分组，同时保留三类人员的明细信息；如果不需要明细，可调整分组字段仅保留部门，聚合所有薪资和工龄。

如果仅需部门级别的统计总和，可简化为：

SELECT
    d.department_name,
    SUM(COALESCE(sup.salary, 0) + COALESCE(sec.salary, 0) + COALESCE(ra.salary, 0)) AS total_salary,
    SUM(COALESCE(sup.tenure, 0) + COALESCE(sec.tenure, 0) + COALESCE(ra.tenure, 0)) AS total_tenure
FROM
    Statistics_Table st
LEFT JOIN People_Table sup ON st.supervisor_id = sup.person_id
LEFT JOIN People_Table sec ON st.secretary_id = sec.person_id
LEFT JOIN People_Table ra ON st.research_assistant = ra.person_id
LEFT JOIN Department_Table d ON st.department_id = d.department_id
GROUP BY
    d.department_name
ORDER BY
    total_salary DESC
LIMIT 20;

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