PostgreSQL查询：统计各岗位人员转入转出数量

DevOps 小满

2026-8-20

解决方案

你可以通过两种常见方式实现需求，将转入、转出统计合并到同一条查询结果中：

方法一：用FULL JOIN合并分组统计

分别统计每个岗位的转入、转出人数，再通过全连接（FULL JOIN）合并结果，用COALESCE处理NULL值（确保无转入/转出记录的岗位显示0而非NULL）：

SELECT
    COALESCE(t_in.position, t_out.position) AS position,
    COALESCE(t_in.transfer_in, 0) AS transfer_in,
    COALESCE(t_out.transfer_out, 0) AS transfer_out
FROM
    -- 统计转入人数：按新岗位分组
    (SELECT new_position AS position, COUNT(*) AS transfer_in
     FROM transfers
     GROUP BY new_position) t_in
FULL JOIN
    -- 统计转出人数：按旧岗位分组
    (SELECT old_position AS position, COUNT(*) AS transfer_out
     FROM transfers
     GROUP BY old_position) t_out
ON t_in.position = t_out.position
ORDER BY position;

方法二：用UNION ALL拆分记录后聚合

将每条调动记录拆分为「转入」「转出」两条标记记录，再按岗位分组统计总数：

SELECT
    position,
    SUM(CASE WHEN transfer_type = 'in' THEN 1 ELSE 0 END) AS transfer_in,
    SUM(CASE WHEN transfer_type = 'out' THEN 1 ELSE 0 END) AS transfer_out
FROM
    (
        -- 标记新岗位为转入
        SELECT new_position AS position, 'in' AS transfer_type FROM transfers
        UNION ALL
        -- 标记旧岗位为转出
        SELECT old_position AS position, 'out' AS transfer_type FROM transfers
    ) combined_records
GROUP BY position
ORDER BY position;