SQL Server双透视关联查询重复记录：需单条聚合结果

问题根源 #

1. 停机统计子查询DownTimeTotals的GROUP BY包含DownMinutes和_Code，导致每个停机代码生成单独行，后续JOIN时与生产表的多行数据匹配，引发笛卡尔积。
2. 直接关联Table2、Table3时，这两张表可能存在同一日期+机器对应多条记录的情况，进一步放大重复。
3. 主查询GROUP BY包含Table3.Width，若同一机器单日存在不同Width值，会拆分分组，导致重复行。

修复方案 #

步骤1：重构停机统计逻辑 #

先按Date, Mach, _Code聚合，计算每个停机代码的总时长和计数，再进行透视，确保每个Date+Mach只生成一行停机数据。

步骤2：预聚合产量数据 #

将Table2和Table3的产量数据先按Date+Mach聚合，避免JOIN时产生重复行。

修改后的完整SQL #

SELECT 
    DT.Date,
    DT.Mach AS MACHINE,
    FORMAT(ISNULL(ROUND(PROD.TotalQty / 3.2808399, 4), 0), 'F', 'it-IT') AS LM_CY,
    FORMAT(ROUND(PROD.TotalSQM, 4), 'F', 'it-IT') AS SQM_CY,
    DT.H_Reason1,
    DT.H_Reason2,
    DT.H_Reason3,
    DT.H_Reason4,
    DT.H_Reason5,
    DT.H_Reason6,
    DT.H_Reason7,
    DT.H_Reason8,
    DT.H_Reason9,
    DT.H_Reason10,
    DT.H_Reason11,
    DT.H_Reason12,
    DT.H_Reason13,
    DT.H_Reason14,
    DT.NUM_Reason1,
    DT.NUM_Reason2,
    DT.NUM_Reason3,
    DT.NUM_Reason4,
    DT.NUM_Reason5,
    DT.NUM_Reason6,
    DT.NUM_Reason7,
    DT.NUM_Reason8,
    DT.NUM_Reason9,
    DT.NUM_Reason10,
    DT.NUM_Reason11,
    DT.NUM_Reason12,
    DT.NUM_Reason13,
    DT.NUM_Reason14
FROM (
    -- 第一步：先聚合停机代码的时长和计数，再透视
    SELECT 
        Date,
        Mach,
        SUM(CASE WHEN _Code = 'Reason1' THEN DownMinutes ELSE 0 END) AS H_Reason1,
        SUM(CASE WHEN _Code = 'Reason2' THEN DownMinutes ELSE 0 END) AS H_Reason2,
        SUM(CASE WHEN _Code = 'Reason3' THEN DownMinutes ELSE 0 END) AS H_Reason3,
        SUM(CASE WHEN _Code = 'Reason4' THEN DownMinutes ELSE 0 END) AS H_Reason4,
        SUM(CASE WHEN _Code = 'Reason5' THEN DownMinutes ELSE 0 END) AS H_Reason5,
        SUM(CASE WHEN _Code = 'Reason6' THEN DownMinutes ELSE 0 END) AS H_Reason6,
        SUM(CASE WHEN _Code = 'Reason7' THEN DownMinutes ELSE 0 END) AS H_Reason7,
        SUM(CASE WHEN _Code = 'Reason8' THEN DownMinutes ELSE 0 END) AS H_Reason8,
        SUM(CASE WHEN _Code = 'Start-up/Shutdown' THEN DownMinutes ELSE 0 END) AS H_Reason9,
        SUM(CASE WHEN _Code = 'Reason10' THEN DownMinutes ELSE 0 END) AS H_Reason10,
        SUM(CASE WHEN _Code = 'Reason11' THEN DownMinutes ELSE 0 END) AS H_Reason11,
        SUM(CASE WHEN _Code = 'Reason12' THEN DownMinutes ELSE 0 END) AS H_Reason12,
        SUM(CASE WHEN _Code = 'Reason13' THEN DownMinutes ELSE 0 END) AS H_Reason13,
        SUM(CASE WHEN ISNULL(_Code, 'Reason14') = 'Reason14' THEN DownMinutes ELSE 0 END) AS H_Reason14,
        COUNT(CASE WHEN _Code = 'Reason1' THEN 1 END) AS NUM_Reason1,
        COUNT(CASE WHEN _Code = 'Reason2' THEN 1 END) AS NUM_Reason2,
        COUNT(CASE WHEN _Code = 'Reason3' THEN 1 END) AS NUM_Reason3,
        COUNT(CASE WHEN _Code = 'Reason4' THEN 1 END) AS NUM_Reason4,
        COUNT(CASE WHEN _Code = 'Reason5' THEN 1 END) AS NUM_Reason5,
        COUNT(CASE WHEN _Code = 'Reason6' THEN 1 END) AS NUM_Reason6,
        COUNT(CASE WHEN _Code = 'Reason7' THEN 1 END) AS NUM_Reason7,
        COUNT(CASE WHEN _Code = 'Reason8' THEN 1 END) AS NUM_Reason8,
        COUNT(CASE WHEN _Code = 'Start-up/Shutdown' THEN 1 END) AS NUM_Reason9,
        COUNT(CASE WHEN _Code = 'Reason10' THEN 1 END) AS NUM_Reason10,
        COUNT(CASE WHEN _Code = 'Reason11' THEN 1 END) AS NUM_Reason11,
        COUNT(CASE WHEN _Code = 'Reason12' THEN 1 END) AS NUM_Reason12,
        COUNT(CASE WHEN _Code = 'Reason13' THEN 1 END) AS NUM_Reason13,
        COUNT(CASE WHEN ISNULL(_Code, 'Reason14') = 'Reason14' THEN 1 END) AS NUM_Reason14
    FROM Database1.dbo.Table1
    GROUP BY Date, Mach
) AS DT
-- 关联预聚合的产量数据
LEFT JOIN (
    SELECT 
        CONVERT(VARCHAR, T2.picked_date, 1) AS ProdDate,
        T2.coater_desc AS Mach,
        SUM(T2.qty) AS TotalQty,
        SUM( (ROUND(T2.qty / 12, 4) * CONVERT(FLOAT, T3.Width)) / 10.7639104 - 1 ) AS TotalSQM
    FROM [Server1].[Database1].dbo.Table2 T2
    INNER JOIN [Server1].[Database1].dbo.Table3 T3 
        ON T2.[MO Number] = T3.MO 
        AND CONVERT(VARCHAR, T2.picked_date, 1) = CONVERT(VARCHAR, T3.Date, 1)
    GROUP BY CONVERT(VARCHAR, T2.picked_date, 1), T2.coater_desc
) AS PROD 
    ON CONVERT(VARCHAR, DT.Date, 1) = PROD.ProdDate 
    AND DT.Mach = PROD.Mach
ORDER BY DT.Date;

关键修改点说明 #

• 停机统计子查询：移除了DownMinutes和_Code的GROUP BY，直接按Date+Mach聚合，用CASE语句计算每个代码的总时长和计数，避免生成多行停机数据。
• 产量预聚合：将Table2和Table3的关联结果先按日期+机器聚合，计算总Qty和总SQM，确保每个Date+Mach只有一行产量数据，消除JOIN时的笛卡尔积。
• JOIN逻辑：使用LEFT JOIN替代INNER JOIN，避免丢失无产量数据的机器日期记录（若不需要可改回INNER JOIN），日期匹配保持原有的CONVERT格式。

内容的提问来源于stack exchange，提问作者C.Mayers