如何用SQL实现含零值的人员年度工作量汇总查询

现有数据表结构及数据 #

1. Years表（存储2010年及以后年份） #

CREATE TABLE Years
(
    YearName int
);
    
INSERT INTO Years (YearName)
VALUES
    (2010), (2011), (2012), (2013),
    (2014), (2015), (2016), (2017),
    (2018), (2019), (2020), (2021),
    (2022), (2023), (2024), (2025)

2. People表（存储人员信息） #

CREATE TABLE People
(
    PersonID int PRIMARY KEY, 
    PersonName varchar(50)
);
    
INSERT INTO People (PersonID, PersonName)
VALUES
    (1, 'Bob'),
    (2, 'Kate'),
    (3, 'Jo'),
    (4, 'Fred');

3. Workload表（存储人员每年不同类型工作的工时） #

CREATE TABLE Workload
(
    ID int PRIMARY KEY, 
    PersonID int, 
    YearName int, 
    WorkType varchar(8), 
    Hours int
);
    
INSERT INTO Workload (ID, PersonID, YearName, WorkType, Hours)
VALUES
    (1, 1, 2014, 'Plumbing', 7),
    (2, 1, 2020, 'Washing', 9),
    (3, 1, 2020, 'Cooking', 10),
    (4, 1, 2020, 'Drawing', 4),
    (5, 1, 2021, 'Reading', 2),
    (6, 2, 2020, 'Washing', 9),
    (7, 2, 2021, 'Cooking', 10),
    (8, 2, 2022, 'Drawing', 4),
    (9, 3, 2014, 'Cooking', 4),
    (10, 3, 2014, 'Plumbing', 22),
    (11, 3, 2015, 'Washing', 7);

当前查询的问题 #

初始查询语句仅能返回有工时记录的人员-年份组合：

SELECT 
    PersonName, YearName, SUM(Hours) AS WorkDone
FROM 
    People p 
INNER JOIN 
    Workload w ON p.PersonID = w.PersonID
WHERE 
    YearName BETWEEN YEAR(GETDATE()) - 9 AND YEAR(GETDATE())
GROUP BY 
    PersonName, YearName

需要实现的效果：每位人员在指定年份范围内的每一年都有记录，无工作记录时Workload列显示0，示例结果如下：

最优解决方案 #

核心思路是先生成所有人员与指定年份的完整组合，再关联工时表统计数据，以下是两种高效实现方式：

方式一：使用CROSS JOIN（基于已有Years表） #

通过CROSS JOIN生成人员与指定年份的全量组合，再用LEFT JOIN关联Workload表，最后用ISNULL将空值转为0：

SELECT 
    p.PersonName AS Person,
    y.YearName AS Year,
    ISNULL(SUM(w.Hours), 0) AS Workload
FROM 
    People p
CROSS JOIN 
    Years y
LEFT JOIN 
    Workload w ON p.PersonID = w.PersonID AND y.YearName = w.YearName
WHERE 
    y.YearName BETWEEN YEAR(GETDATE()) - 9 AND YEAR(GETDATE())
GROUP BY 
    p.PersonName, y.YearName
ORDER BY 
    p.PersonName, y.YearName

方式二：使用CROSS APPLY（动态生成年份） #

如果不需要依赖Years表，可通过CROSS APPLY动态生成目标年份范围，再关联人员和工时表：

SELECT 
    p.PersonName AS Person,
    y.YearName AS Year,
    ISNULL(SUM(w.Hours), 0) AS Workload
FROM 
    People p
CROSS APPLY (
    -- 动态生成近9年的年份
    SELECT YEAR(GETDATE()) - n AS YearName
    FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS nums(n)
) y
LEFT JOIN 
    Workload w ON p.PersonID = w.PersonID AND y.YearName = w.YearName
GROUP BY 
    p.PersonName, y.YearName
ORDER BY 
    p.PersonName, y.YearName

关键说明 #

1. 组合生成逻辑：CROSS JOIN适用于已有年份表的场景，CROSS APPLY更适合动态生成年份的需求。
2. 筛选位置：年份范围筛选必须针对基础的年份数据源（Years表或动态生成的年份），不能放在LEFT JOIN的ON条件中，否则会过滤掉无工时的记录。
3. 空值处理：LEFT JOIN后无工时的记录Hours为NULL，用ISNULL(SUM(w.Hours),0)将其转为0，符合需求格式。

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