如何用SQL将工时数据行转列并补全缺失的7天日期？

核心思路是通过条件聚合实现行转列，同时处理缺失日期的填充逻辑，确保1-7日的工时列在无数据时显示0.0，最后按年周分组计算总工时。以下是主流SQL方言的实现示例：

MySQL 实现 #

SELECT
    YearNum,
    WeekNum,
    SUM(CASE WHEN DayNum = 1 THEN Hours ELSE 0.0 END) AS Day1_Hours,
    SUM(CASE WHEN DayNum = 2 THEN Hours ELSE 0.0 END) AS Day2_Hours,
    SUM(CASE WHEN DayNum = 3 THEN Hours ELSE 0.0 END) AS Day3_Hours,
    SUM(CASE WHEN DayNum = 4 THEN Hours ELSE 0.0 END) AS Day4_Hours,
    SUM(CASE WHEN DayNum = 5 THEN Hours ELSE 0.0 END) AS Day5_Hours,
    SUM(CASE WHEN DayNum = 6 THEN Hours ELSE 0.0 END) AS Day6_Hours,
    SUM(CASE WHEN DayNum = 7 THEN Hours ELSE 0.0 END) AS Day7_Hours,
    SUM(Hours) AS Total_Hours
FROM
    your_input_table
GROUP BY
    YearNum, WeekNum
ORDER BY
    YearNum, WeekNum;

• 用CASE WHEN匹配每日数据，无匹配时返回0.0，通过SUM聚合得到当日总工时
• GROUP BY YearNum, WeekNum确保按年周维度分组
• SUM(Hours)直接计算该周总工时，比累加每日列更高效准确

SQL Server 实现 #

SELECT
    YearNum,
    WeekNum,
    ISNULL(SUM(CASE WHEN DayNum = 1 THEN Hours END), 0.0) AS Day1_Hours,
    ISNULL(SUM(CASE WHEN DayNum = 2 THEN Hours END), 0.0) AS Day2_Hours,
    ISNULL(SUM(CASE WHEN DayNum = 3 THEN Hours END), 0.0) AS Day3_Hours,
    ISNULL(SUM(CASE WHEN DayNum = 4 THEN Hours END), 0.0) AS Day4_Hours,
    ISNULL(SUM(CASE WHEN DayNum = 5 THEN Hours END), 0.0) AS Day5_Hours,
    ISNULL(SUM(CASE WHEN DayNum = 6 THEN Hours END), 0.0) AS Day6_Hours,
    ISNULL(SUM(CASE WHEN DayNum = 7 THEN Hours END), 0.0) AS Day7_Hours,
    SUM(Hours) AS Total_Hours
FROM
    your_input_table
GROUP BY
    YearNum, WeekNum
ORDER BY
    YearNum, WeekNum;

• 用ISNULL处理无数据时SUM返回的NULL，将其转换为0.0
• 逻辑与MySQL一致，适配SQL Server的语法特性

PostgreSQL 实现 #

SELECT
    YearNum,
    WeekNum,
    COALESCE(SUM(CASE WHEN DayNum = 1 THEN Hours END), 0.0) AS Day1_Hours,
    COALESCE(SUM(CASE WHEN DayNum = 2 THEN Hours END), 0.0) AS Day2_Hours,
    COALESCE(SUM(CASE WHEN DayNum = 3 THEN Hours END), 0.0) AS Day3_Hours,
    COALESCE(SUM(CASE WHEN DayNum = 4 THEN Hours END), 0.0) AS Day4_Hours,
    COALESCE(SUM(CASE WHEN DayNum = 5 THEN Hours END), 0.0) AS Day5_Hours,
    COALESCE(SUM(CASE WHEN DayNum = 6 THEN Hours END), 0.0) AS Day6_Hours,
    COALESCE(SUM(CASE WHEN DayNum = 7 THEN Hours END), 0.0) AS Day7_Hours,
    SUM(Hours) AS Total_Hours
FROM
    your_input_table
GROUP BY
    YearNum, WeekNum
ORDER BY
    YearNum, WeekNum;

• 用COALESCE替代ISNULL，实现PostgreSQL下的NULL值转换

注意事项 #

• 替换your_input_table为实际的输入表名
• 如果DayNum存在非1-7的无效值，可添加WHERE DayNum BETWEEN 1 AND 7过滤数据
• 若需要确保所有年周（即使无任何工时数据）都出现在结果中，可关联一个包含所有1-7日的日历表进行左连接

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