SQL多列分组统计问题：按月份统计计划与实际销售数量

需求 #

获取按月份统计计划数量（PlanCount）与实际数量（ActualCount）的销售数据。

原始数据 #

PlanMonth       ActualMth
Feb - 2018  Jan - 2018
Feb - 2018  Dec - 2017
Jan - 2018  Jan - 2018
Jan - 2018  Feb - 2018
Jan - 2018  Jan - 2018
Jan - 2018  Feb - 2018
Feb - 2018  Feb - 2018
Mar -2018       May - 2018
                Nov - 2018
                Feb - 2018

期望输出 #

Month       PlanCount ActualCount  
Feb - 2018    3         4
Jan - 2018    4         3
May -2018    Null       1
Nov - 2018   Null       1
Mar -2018    1         Null

尝试的查询语句 #

Select ActualMonth, Count(ActualMonth), PlanMonth, Count(PlanMonth)
from table
group by ActualMonth, PlanMonth

错误结果 #

PlanMonth  PlanCount ActualMonth ActualCount  
Jan-18       1            Jan-18        1   
Jan-18       2            Jan-18        2
Feb-2018     2            Feb - 2018    2
Feb-2018     2            Jan - 2018    2

原查询错误的核心原因是同时按ActualMonth和PlanMonth分组，导致每一对计划-实际月份组合被单独统计，而非按统一月份汇总两类数据。以下是两种可行的解决方法：

方法一：CTE收集全量月份+关联统计 #

WITH AllMonths AS (
    -- 收集所有出现过的月份（计划和实际）
    SELECT PlanMonth AS Month FROM your_table
    UNION
    SELECT ActualMth AS Month FROM your_table
),
PlanStats AS (
    -- 统计每个月份的计划数量
    SELECT PlanMonth AS Month, COUNT(*) AS PlanCount
    FROM your_table
    WHERE PlanMonth IS NOT NULL
    GROUP BY PlanMonth
),
ActualStats AS (
    -- 统计每个月份的实际数量
    SELECT ActualMth AS Month, COUNT(*) AS ActualCount
    FROM your_table
    WHERE ActualMth IS NOT NULL
    GROUP BY ActualMth
)
SELECT 
    am.Month,
    ps.PlanCount,
    as_.ActualCount
FROM AllMonths am
LEFT JOIN PlanStats ps ON am.Month = ps.Month
LEFT JOIN ActualStats as_ ON am.Month = as_.Month
-- 按月份时间排序（需根据数据库调整函数）
ORDER BY STR_TO_DATE(am.Month, '%b - %Y') DESC;

方法二：全外连接+子查询统计 #

SELECT 
    COALESCE(p.Month, a.Month) AS Month,
    p.PlanCount,
    a.ActualCount
FROM (
    SELECT PlanMonth AS Month, COUNT(*) AS PlanCount
    FROM your_table
    WHERE PlanMonth IS NOT NULL
    GROUP BY PlanMonth
) p
FULL OUTER JOIN (
    SELECT ActualMth AS Month, COUNT(*) AS ActualCount
    FROM your_table
    WHERE ActualMth IS NOT NULL
    GROUP BY ActualMth
) a ON p.Month = a.Month
-- 按月份时间排序（需根据数据库调整函数）
ORDER BY STR_TO_DATE(COALESCE(p.Month, a.Month), '%b - %Y') DESC;

注意事项 #

1. 替换语句中的your_table为实际表名
2. 日期排序函数需适配所用数据库：
   • MySQL：STR_TO_DATE(Month, '%b - %Y')
   • SQL Server：CONVERT(DATE, Month, 106)
   • Oracle：TO_DATE(Month, 'Mon - YYYY')
3. 若某月份只有计划/实际数据，对应另一列会显示NULL，符合期望输出要求

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