如何将两个按月统计的SQL查询结果合并为包含计划、实际值的表

你可以通过公共表表达式（CTE）分别封装两个统计逻辑，再以计划人数的统计结果为基准做左关联，即可实现需求，无实际达标数据的月份可通过COALESCE函数将空值转为0。

完整实现代码如下：

WITH plan_stats AS (
  -- 原计划人数统计查询
  SELECT COUNT(DISTINCT PARTICIPANT_ID) AS PLAN,
  (TO_CHAR(SCHEDULED_START_DATETIME, 'YYYY-MM'))AS MONTH
  FROM(
    SELECT  
    MIN(COALESCE(CSD.RESCHEDULED_START_DATETIME, CSD.SCHEDULED_START_DATETIME)) AS SCHEDULED_START_DATETIME, 
    MAX(COALESCE(CSD.RESCHEDULED_END_DATETIME, CSD.SCHEDULED_END_DATETIME)) AS SCHEDULED_END_DATETIME,
    CP.PARTICIPANT_ID, count(distinct participant_id)
    FROM ERP.COURSE_PARTICIPANT AS CP
    INNER JOIN ERP.COURSE_SCHEDULE_DETAIL AS CSD
    ON CP.COURSE_SCHEDULE_ID = CSD.COURSE_SCHEDULE_ID
    INNER JOIN ERP.COURSE_SCHEDULE AS CS
    ON CSD.ID = CS.ID
    INNER JOIN ERP.COURSE AS C
    ON CS.COURSE_ID = C.ID
    INNER JOIN ERP.COURSE_CATEGORY AS CC
    ON C.COURSE_CATEGORY_ID = CC.ID
    INNER JOIN ERP.EMPLOYEE AS E
    ON CP.PARTICIPANT_ID = E.ID
    INNER JOIN ERP.MEMBER_ROLE AS MR
    ON E.MEMBER_ROLE_ID = MR.ID
    WHERE C.MANDATORY = 'Yes'
    AND MR.ROLE_TYPE = 'Dev'
    AND CC.CATEGORY = 'Programmer'
    GROUP BY CP.PARTICIPANT_ID) AS COURSE_PARTICIPANT
  GROUP BY MONTH
),
actual_stats AS (
  -- 补全开头缺失字段后的实际达标人数统计查询
  SELECT COUNT(DISTINCT PARTICIPANT_ID) AS ACTUAL,
  TO_CHAR(LOG_IN_DATETIME, 'YYYY-MM') AS MONTH
  FROM(
    SELECT MAX(COALESCE(CA.LOG_IN_DATETIME, CA.LOG_OUT_DATETIME)) AS LOG_IN_DATETIME,
    MAX(COALESCE(CA.LOG_OUT_DATETIME, CA.LOG_IN_DATETIME)) AS LOG_OUT_DATETIME,
    CA.PARTICIPANT_ID, COUNT(DISTINCT C.NAME) AS COURSE
    FROM ERP.COURSE_ATTENDANCE CA
    INNER JOIN ERP.COURSE_SCHEDULE_DETAIL AS CSD
    ON CA.COURSE_SCHEDULE_DETAIL_ID  = CSD.COURSE_SCHEDULE_ID
    INNER JOIN ERP.COURSE_SCHEDULE AS CS
    ON CSD.COURSE_SCHEDULE_ID = CS.ID 
    INNER JOIN ERP.COURSE AS C
    ON CS.COURSE_ID = C.ID
    INNER JOIN ERP.COURSE_CATEGORY AS CC
    ON C.COURSE_CATEGORY_ID = CC.ID
    INNER JOIN ERP.EMPLOYEE AS E
    ON CA.PARTICIPANT_ID = E.ID
    INNER JOIN ERP.MEMBER_ROLE AS MR
    ON E.MEMBER_ROLE_ID = MR.ID
    WHERE CA.STATUS = 'P' 
    AND C.MANDATORY = 'Yes' 
    AND MR.ROLE_TYPE = 'Dev'
    AND CC.CATEGORY = 'Programmer'
    GROUP BY PARTICIPANT_ID
    HAVING COUNT(C.NAME) >= (SELECT COUNT(*) FROM ERP.COURSE C
    JOIN ERP.COURSE_CATEGORY CC
    ON C.COURSE_CATEGORY_ID = CC.ID
    WHERE CC.CATEGORY = 'Programmer')
  )AS COURSE_ATTENDANCE
  WHERE TO_CHAR(LOG_IN_DATETIME, 'YYYY-MM') BETWEEN '2021-01' AND '2021-12'
  GROUP BY MONTH
)
-- 关联两个统计结果输出最终表
SELECT 
  p.PLAN,
  COALESCE(a.ACTUAL, 0) AS ACTUAL, -- 去掉COALESCE函数则无数据时显示NULL
  p.MONTH
FROM plan_stats p
LEFT JOIN actual_stats a ON p.MONTH = a.MONTH
ORDER BY p.MONTH;

补充说明 #

• 如果你需要输出2021年全部12个月份的数据（哪怕某月份没有计划人数），可以先生成一个包含2021年所有月份的临时表作为关联基准，再同时左关联两个统计结果即可。
• 若你使用的数据库不支持CTE语法，也可以把两个统计查询直接写为FROM后的子查询进行关联，逻辑完全一致。

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