如何为每个有数据的零件生成全天每小时的记录？

需要为每个有数据的零件类型生成一天中每个小时的记录，目前仅能显示Hour列的小时值，无法正确展示零件信息。尝试将目标数据查询语句与小时表通过记录的小时时间戳关联，实现每个零件组对应全天每小时的行，但未成功。

尝试的代码 #

DECLARE @Date DATETIME = GETDATE()
--生成一天中每个小时的字段（12AM - 11PM）
;WITH Dates AS
(
    SELECT FORMAT(DATEADD(HOUR, -1, @Date),'h tt') [Hour], 
           DATEADD(HOUR,-1,@Date) [Date], 
           1 Num
    UNION ALL
    SELECT FORMAT(DATEADD(HOUR, -1, [Date]),'h tt'), 
           DATEADD(HOUR,-1,[Date]), 
           Num+1
    FROM Dates
    WHERE Num <= 23
)
, PickedData AS
(SELECT [Hour], x.group_area, x.HC, x.HCSum, x.picked_user, x.pickedDateTime, x.rate, x.reaches, x.ShiftOutput
    FROM Dates
    LEFT OUTER JOIN (包含小时时间戳及其他列的PICKEDDATA) x
    ON x.hourTimestamp = [Dates].Hour
 )

SELECT group_area, PD.HC, PD.reaches, PD.Hour
FROM PickedData PD
JOIN Dates D
    ON D.[Hour] = PD.[Hour]
ORDER BY PD.pickedDateTime

当前结果 #

预期结果示例（6AM到5PM） #

解决方案 #

原代码的核心问题是没有先获取所有唯一零件类型，再与小时表做全量组合。以下是修正后的实现逻辑：

1. 生成全天24小时的时间范围（用原始时间值关联，避免格式化字符串匹配误差）
2. 获取当前日期下所有有数据的唯一零件类型
3. 交叉连接零件类型和小时表，得到每个零件对应每个小时的基础框架
4. 左连接实际业务数据，匹配零件类型和小时时间戳

修正后的代码 #

DECLARE @Date DATETIME = CAST(GETDATE() AS DATE); -- 取当前日期，去除时间部分

-- 生成全天24小时的时间范围，保留原始时间值用于关联，同时生成格式化的显示字段
;WITH Hours AS (
    SELECT 
        DATEADD(HOUR, n, @Date) AS HourStart, -- 每个小时的起始时间
        FORMAT(DATEADD(HOUR, n, @Date), 'h tt') AS HourDisplay
    FROM (
        SELECT TOP 24 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
        FROM sys.all_columns
    ) nums
),
-- 获取当前日期下所有有数据的唯一零件类型
PartTypes AS (
    SELECT DISTINCT group_area AS Part
    FROM PICKEDDATA -- 替换为你的实际表名
    WHERE CAST(pickedDateTime AS DATE) = @Date
)
-- 交叉连接零件类型和小时表，再左连业务数据
SELECT 
    pt.Part,
    pd.HC,
    pd.HCSum,
    pd.picked_user,
    pd.pickedDateTime,
    pd.rate,
    pd.reaches,
    pd.ShiftOutput,
    h.HourDisplay AS Hour
FROM PartTypes pt
CROSS JOIN Hours h
LEFT JOIN PICKEDDATA pd 
    ON pt.Part = pd.group_area
    AND CAST(pd.pickedDateTime AS DATE) = @Date
    AND DATEPART(HOUR, pd.pickedDateTime) = DATEPART(HOUR, h.HourStart)
ORDER BY pt.Part, h.HourStart;

关键说明 #

• 使用CROSS JOIN确保每个零件都能生成24小时的记录，无论该小时是否有数据
• 用DATEPART(HOUR, ...)匹配小时数，避免格式化字符串（如"9 AM"）的匹配误差
• 限定日期范围，确保只处理当天的数据
• 先提取唯一零件类型，避免无效的空零件记录

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