如何在SQL Azure中返回含无数据年份的年度累计工时

我在SQL Azure中有如下结构的数据库：

CREATE TABLE Years
(
    YearID INT IDENTITY PRIMARY KEY,
    YearName VARCHAR(8)
)

INSERT INTO Years (YearName)
VALUES ('2017-18'),('2018-19'),('2019-20'),('2020-21'),('2021-22'),('2022-23')

CREATE TABLE People
(
    PersonID INT PRIMARY KEY IDENTITY,
    FirstName VARCHAR(100)
)

INSERT INTO People (FirstName)
VALUES ('Fred'), ('Bob'), ('Samit'), ('Kate')

CREATE TABLE WorkCategories 
(
    CatID INT IDENTITY PRIMARY KEY,
    CatName VARCHAR(100)
)

INSERT INTO WorkCategories (CatName) 
VALUES ('Testing'), ('Examining'), ('Teaching'), ('Marking')

CREATE TABLE WorkDone
(
    WorkID INT PRIMARY KEY IDENTITY,
    PersonID INT FOREIGN KEY REFERENCES People(PersonID),
    WorkCategory INT FOREIGN KEY REFERENCES WorkCategories(CatID),
    YearID INT FOREIGN KEY REFERENCES Years(YearID),
    HoursWorked INT
)

INSERT INTO WorkDone (PersonID, WorkCategory, YearID, HoursWorked)
VALUES (1, 1, 1, 10), (1, 1, 2, 30), (1, 1, 3, 15),
       (1, 1, 6, 25), (1, 2, 1, 24), (1, 2, 4, 28), 
       (1, 2, 4, 28), (1, 2, 5, 20), (1, 2, 6, 10)

我需要获取指定年份下，每个人各工作类别的累计工时。目前已创建如下视图：

CREATE VIEW vw_TotalWorkDone
AS
    SELECT
        y.YearName,
        WorkInfo.PersonID, WorkInfo.CatName, 
        WorkInfo.YearID, WorkInfo.TotalHours,
        SUM(WorkInfo.TotalHours) OVER 
(PARTITION BY WorkInfo.PersonID, WorkInfo.CatName ORDER BY y.YearName DESC) AS TotalSinceDate
    FROM 
        Years y
    LEFT JOIN 
        (SELECT
             wc.CatName, wd.PersonID, wd.YearID,
             SUM(wd.HoursWorked) AS TotalHours
         FROM 
             WorkCategories wc
         LEFT JOIN 
             WorkDone wd ON wc.CatID = wd.WorkCategory
         GROUP BY 
             wc.CatName, wd.PersonID, wd.YearID) WorkInfo ON y.YearID = WorkInfo.YearID

该视图返回的结果准确，但存在问题：当查询指定年份（如2018-19）的某人员数据时，若该人员当年无某类工作记录（如Examining），则该类别不会被展示。我需要实现：所有有过工作记录的类别，每个年份都要显示，即使该年无对应工时（显示0），且累计工时需正确回溯到该年份，示例结果如下：

（注：修正了原示例中2019-20和2018-19行的YearID错误）

核心问题在于原视图未生成人员、工作类别、年份的全量有效组合，导致缺失无工时的行。我们需要先构建这三者的笛卡尔积（仅包含有过工作记录的人员和类别），再关联实际工时数据，最后计算累计值。

修改后的视图如下：

CREATE VIEW vw_TotalWorkDone
AS
WITH PersonWorkCategories AS (
    -- 获取所有有过工作记录的人员-类别组合
    SELECT DISTINCT wd.PersonID, wc.CatID, wc.CatName
    FROM WorkDone wd
    JOIN WorkCategories wc ON wd.WorkCategory = wc.CatID
),
YearPersonCategory AS (
    -- 生成年份-人员-类别的全量组合
    SELECT y.YearID, y.YearName, pwc.PersonID, pwc.CatName
    FROM Years y
    CROSS JOIN PersonWorkCategories pwc
),
WorkHours AS (
    -- 预先计算每个人员-类别-年份的总工时
    SELECT 
        wd.PersonID, 
        wc.CatName, 
        wd.YearID, 
        SUM(wd.HoursWorked) AS TotalHours
    FROM WorkDone wd
    JOIN WorkCategories wc ON wd.WorkCategory = wc.CatID
    GROUP BY wd.PersonID, wc.CatName, wd.YearID
)
SELECT 
    ypc.YearName,
    ypc.PersonID,
    ypc.CatName,
    ypc.YearID,
    ISNULL(wh.TotalHours, 0) AS TotalHours,
    -- 按人员-类别分组，从当前年份往前累计
    SUM(ISNULL(wh.TotalHours, 0)) OVER (
        PARTITION BY ypc.PersonID, ypc.CatName 
        ORDER BY ypc.YearID DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS TotalSinceDate
FROM YearPersonCategory ypc
LEFT JOIN WorkHours wh 
    ON ypc.PersonID = wh.PersonID 
    AND ypc.CatName = wh.CatName 
    AND ypc.YearID = wh.YearID

代码说明 #

1. PersonWorkCategories：筛选出所有实际有过工作记录的人员和类别组合，避免生成完全无数据的无效组合（比如Bob和所有类别）。
2. YearPersonCategory：通过CROSS JOIN生成年份与人员-类别组合的全量笛卡尔积，确保每个年份、每个有过记录的人员-类别都有一行。
3. WorkHours：预先聚合每个人员-类别-年份的总工时，提升查询效率。
4. 最后关联工时数据，用ISNULL将无工时的行转为0，再通过窗口函数计算从当前年份开始的累计工时（按年份倒序，累计当前及更早年份的总和）。

验证查询 #

查询指定人员和年份的示例：

SELECT * 
FROM vw_TotalWorkDone
WHERE PersonID = 1 AND YearName = '2018-19'
ORDER BY CatName, YearName DESC

会返回该人员所有有过记录的类别，即使当年无工时也显示0，且累计值正确。

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