如何按年月维度展示所有Tenure分组数据，无数据时填充0？

我需要统计各年月（YearMonth）下不同Tenure分组的员工招聘人数，但当前执行的SQL仅显示有数据的分组。希望实现每个年月都包含所有Tenure分组，无对应数据时招聘人数显示为0。

原查询语句 #

;WITH Tenure
AS
(
    SELECT 
        T.[Employee ID],
        CASE WHEN [Datediff_] = logic THEN '0 to 1 Year'
             WHEN [Datediff_] = logic THEN '1 to 1.11 Year'
             WHEN [Datediff_] = logic THEN '2 to 2.11 Year'
             WHEN [Datediff_] = logic THEN '3 to 3.11 Year'
             WHEN [Datediff_] = logic THEN '4 to 4.11 Year' 
             ELSE 'More Than 5 Years' END AS [Tenure Grouping]          
    FROM
    (
        SELECT 
            DISTINCT [Employee ID],[Hire Date],[Last Working Day],
            CASE WHEN logic END AS [Datediff_] -- 此处logic为实际计算tenure的逻辑，需替换为真实代码
        FROM [dbo].[Employee_]
    )AS T
)

,Headcount
AS
(
    SELECT DISTINCT 
            FORMAT(T1.HireDate,'yyyyMM') AS [Month Year],
            T2.[Tenure Grouping],
            COUNT(T1.[Employee ID]) AS Headcount
        FROM [dbo].[Employee_] AS T1 WITH (NOLOCK)
        LEFT OUTER JOIN Tenure AS T2 WITH (NOLOCK) ON T1.[Employee ID] = T2.[Employee ID]
)

SELECT * FROM Headcount

当前执行结果 #

期望结果 #

注意：所有年月都需要包含所有Tenure分类，无数据时显示0

解决方案 #

核心思路是先生成所有年月和所有Tenure分组的完整组合，再左连接实际的统计数据，从而补全缺失的分组并将NULL转为0。修改后的SQL如下：

-- 1. 定义所有固定的Tenure分组（确保覆盖所有需要的分类）
;WITH AllTenureGroups AS (
    SELECT '0 to 1 Year' AS [Tenure Grouping] UNION ALL
    SELECT '1 to 1.11 Year' UNION ALL
    SELECT '2 to 2.11 Year' UNION ALL
    SELECT '3 to 3.11 Year' UNION ALL
    SELECT '4 to 4.11 Year' UNION ALL
    SELECT 'More Than 5 Years'
),
-- 2. 提取所有存在的招聘年月
AllHireMonths AS (
    SELECT DISTINCT FORMAT(HireDate, 'yyyyMM') AS [YearMonth]
    FROM [dbo].[Employee_] WITH (NOLOCK)
),
-- 3. 生成年月和Tenure分组的所有可能组合
AllMonthTenureCombos AS (
    SELECT 
        ahm.[YearMonth],
        atg.[Tenure Grouping]
    FROM AllHireMonths ahm
    CROSS JOIN AllTenureGroups atg
),
-- 4. 计算各年月各Tenure分组的实际招聘人数
ActualHiringStats AS (
    SELECT
        FORMAT(T1.HireDate, 'yyyyMM') AS [YearMonth],
        T2.[Tenure Grouping],
        COUNT(T1.[Employee ID]) AS [Hiring]
    FROM [dbo].[Employee_] T1 WITH (NOLOCK)
    JOIN (
        -- 这里保留你原有的Tenure计算逻辑，替换掉logic占位符
        SELECT 
            [Employee ID],
            CASE 
                WHEN DATEDIFF(MONTH, [Hire Date], [Last Working Day]) BETWEEN 0 AND 11 THEN '0 to 1 Year'
                WHEN DATEDIFF(MONTH, [Hire Date], [Last Working Day]) BETWEEN 12 AND 23 THEN '1 to 1.11 Year'
                WHEN DATEDIFF(MONTH, [Hire Date], [Last Working Day]) BETWEEN 24 AND 35 THEN '2 to 2.11 Year'
                WHEN DATEDIFF(MONTH, [Hire Date], [Last Working Day]) BETWEEN 36 AND 47 THEN '3 to 3.11 Year'
                WHEN DATEDIFF(MONTH, [Hire Date], [Last Working Day]) BETWEEN 48 AND 59 THEN '4 to 4.11 Year'
                ELSE 'More Than 5 Years' 
            END AS [Tenure Grouping]
        FROM [dbo].[Employee_] WITH (NOLOCK)
    ) T2 ON T1.[Employee ID] = T2.[Employee ID]
    GROUP BY FORMAT(T1.HireDate, 'yyyyMM'), T2.[Tenure Grouping]
)
-- 5. 左连接组合表和实际统计，补全0值
SELECT
    amtc.[YearMonth],
    amtc.[Tenure Grouping],
    ISNULL(ahs.[Hiring], 0) AS [Hiring]
FROM AllMonthTenureCombos amtc
LEFT JOIN ActualHiringStats ahs 
    ON amtc.[YearMonth] = ahs.[YearMonth] 
    AND amtc.[Tenure Grouping] = ahs.[Tenure Grouping]
ORDER BY amtc.[YearMonth], 
    -- 按分组顺序排序，确保结果整齐
    CASE amtc.[Tenure Grouping]
        WHEN '0 to 1 Year' THEN 1
        WHEN '1 to 1.11 Year' THEN 2
        WHEN '2 to 2.11 Year' THEN 3
        WHEN '3 to 3.11 Year' THEN 4
        WHEN '4 to 4.11 Year' THEN 5
        WHEN 'More Than 5 Years' THEN 6
    END

关键说明 #

1. AllTenureGroups：手动定义所有需要的Tenure分组，确保不会因为员工表中无对应数据而遗漏分组。
2. CROSS JOIN：生成年月和分组的笛卡尔积，得到所有可能的组合。
3. ISNULL(ahs.[Hiring], 0)：将左连接后无数据的NULL值转为0。
4. 原Tenure计算逻辑中的logic占位符已替换为示例的月份差计算，你需要根据实际业务逻辑调整这部分代码。

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