如何修改存储过程以生成含多设备行的车辆运营临时报表？

问题根源 #

你当前的存储过程大概率是在关联表时错误做了聚合（比如用GROUP BY合并了同一车辆的设备记录），或是关联逻辑只取了单条Require_Detail记录，导致每个车辆仅显示一个设备。要实现每个设备一行、首行填充运营字段的效果，需要保留Require_Detail的多条关联记录，并通过窗口函数标记首行来控制字段显示。

解决方案步骤 #

1. 正确关联所有表，保留多设备记录 #

确保从Car_Info到Require_Detail的关联逻辑正确，保留每个车辆对应的所有待修设备记录，不要提前聚合。参考关联关系（需根据实际表结构调整字段）：

• Car_Info与Operation通过ID_Car关联
• Operation与Require_Overview通过Order_Number关联
• Require_Overview与Require_Detail通过ID_Require关联

2. 使用窗口函数标记首行 #

用ROW_NUMBER()窗口函数，按车辆和订单的唯一标识分组，为每个车辆的设备行编号。首行编号为1，后续设备行编号递增。

3. 根据行号控制字段显示 #

行号=1的记录保留Order_Number、ID_Car等运营字段的值；行号>1的记录将这些字段设为NULL或空字符串，仅保留Name_Equipment和必要关联字段（如ID_Require）。

修改后的存储过程示例 #

CREATE PROCEDURE MONTH_REPORT
    @Month VARCHAR(2),
    @Year VARCHAR(4)
AS
BEGIN
    SET NOCOUNT ON;

    -- 清空并重建临时表
    IF OBJECT_ID('tempdb..#Report') IS NOT NULL
        DROP TABLE #Report;

    CREATE TABLE #Report (
        Order_Number VARCHAR(50),
        ID_Car VARCHAR(50),
        ID_Operation VARCHAR(50),
        Travel_Distance DECIMAL(18,2),
        Month VARCHAR(2),
        Year VARCHAR(4),
        ID_Require VARCHAR(50),
        Name_Equipment VARCHAR(100)
    );

    -- 插入处理后的数据
    INSERT INTO #Report (
        Order_Number, ID_Car, ID_Operation, Travel_Distance, Month, Year, ID_Require, Name_Equipment
    )
    SELECT
        CASE WHEN RowNum = 1 THEN o.Order_Number ELSE NULL END AS Order_Number,
        CASE WHEN RowNum = 1 THEN ci.ID_Car ELSE NULL END AS ID_Car,
        CASE WHEN RowNum = 1 THEN o.ID_Operation ELSE NULL END AS ID_Operation,
        CASE WHEN RowNum = 1 THEN o.Travel_Distance ELSE NULL END AS Travel_Distance,
        @Month AS Month,
        @Year AS Year,
        rd.ID_Require,
        rd.Name_Equipment
    FROM
        Car_Info ci
    JOIN
        Operation o ON ci.ID_Car = o.ID_Car
    JOIN
        Require_Overview ro ON o.Order_Number = ro.Order_Number
    JOIN
        Require_Detail rd ON ro.ID_Require = rd.ID_Require
    -- 按实际日期字段过滤指定年月数据
    WHERE
        MONTH(o.Operation_Date) = @Month
        AND YEAR(o.Operation_Date) = @Year
    -- 生成行号，按车辆+订单分组排序
    CROSS APPLY (
        SELECT ROW_NUMBER() OVER (PARTITION BY ci.ID_Car, o.Order_Number ORDER BY rd.ID_Require) AS RowNum
    ) rn
    ORDER BY
        ci.ID_Car, o.Order_Number, RowNum;

    -- 可选：查询临时表用于测试
    SELECT * FROM #Report;
END
GO

关键注意事项 #

• 调整关联字段：代码中的关联条件（如ci.ID_Car = o.ID_Car）需匹配你的实际表结构，避免关联错误。
• 日期过滤：WHERE子句中的o.Operation_Date需替换为你存储运营年月的实际字段，确保筛选正确数据。
• 空值处理：若要显示空字符串而非NULL，可将ELSE NULL改为ELSE ''。

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