为什么我得到以下SQL查询的重复记录

Question

我有以下疑问：

SELECT 
    l.ModelTypeIndexNumber AS [Model Index Number],
    l.TestGroupname AS [Test Group],
    lf.CE_Fuel AS [Fuel],
    CASE 
        WHEN lf.Electricity = '' THEN '(missing)' \
        ELSE  lf.Electricity 
    END AS [Electric Motor Energy Source],
    tg.TG_Fuel + ', EL' AS [TG Fuel Sources],
    CASE 
        WHEN l.LabelCalculationApproachIdentifier ='PHEV' THEN  'PHEV'
        ELSE '(Not PHEV)' 
    END AS [Label Calculation Approach] 
FROM Relational.Label l 
INNER JOIN (
    SELECT 
        lbl.SubmissionId, 
        lbl.ModelTypeIndexNumber, 
        COUNT(*) AS lblFuelCount, 
        MAX(CASE 
                WHEN fu.FuelUsageIdentifier <>'EL' THEN fu.FuelUsageIdentifier 
                ELSE NULL 
            END) AS CE_Fuel, 
        MAX(CASE 
                WHEN fu.FuelUsageIdentifier ='EL' THEN fu.FuelUsageIdentifier 
                ELSE '' 
            END) AS Electricity 
    FROM Relational.Label lbl 
    INNER JOIN Relational.FuelUsage fu 
        ON lbl.LabelId=fu.LabelId 
    WHERE lbl.SubmissionId=@SubmissionId 
    GROUP BY lbl.SubmissionId, lbl.ModelTypeIndexNumber 
    ) AS lf 
    ON l.SubmissionId=lf.SubmissionId 
INNER JOIN (
    SELECT 
        rtg.SubmissionId, 
        rtg.TestGroupName, 
        MAX(CASE 
            WHEN ds.FuelId <> 'EL' THEN ds.FuelId 
            ELSE NULL 
        END) AS TG_Fuel 
    FROM Relational.DriveSource ds 
    INNER JOIN Relational.TestGroup rtg 
        ON ds.TestGroupId=rtg.TestGroupId 
    WHERE rtg.SubmissionId=@SubmissionId 
    GROUP BY rtg.SubmissionId, rtg.TestGroupName 
    HAVING COUNT(*)>1 AND MAX(CASE WHEN ds.FuelId ='EL' THEN 1 ELSE 0 END) = 1
    ) AS tg 
    ON lf.SubmissionId=tg.SubmissionId 
    AND l.TestGroupName = tg.TestGroupName 
WHERE (lf.Electricity = '' AND l.LabelCalculationApproachIdentifier = 'PHEV') 

它将生成以下输出：

Model Index Number    Test Group    Fuel    TG Fuel Source(s)    Label Calculation Approach   
19                    ABC.1987      GP      DX, EL               PHEV
19                    XYZ.1989      DM      DS, EL               PHEV
20                    ABC.1987      GP      DX. EL               PHEV
20                    XYZ.1989      DM      DS, EL               PHEV

但我期望得到以下结果：

Model Index Number    Test Group    Fuel    TG Fuel Source(s)    Label Calculation Approach
19                    ABC.1987      GP      DX, EL               PHEV
20                    XYZ.1989      DM      DS, EL               PHEV

我不想重复。也许会加入这个问题。我试过DISTINCT。我在查询中做错了什么？

Answer 1

为什么我得到重复的记录？

这个问题的答案在很大程度上取决于源表中的数据。

这只是一个猜测(因为我没有您的真实表和数据，所以无法运行查询)：

我认为在查询的末尾也需要一条GROUP BY语句。查询可能如下所示：

SELECT 
    l.ModelTypeIndexNumber AS [Model Index Number],
    l.TestGroupname AS [Test Group],
    MAX(lf.CE_Fuel AS [Fuel]),
    MAX(CASE 
        WHEN lf.Electricity = '' THEN '(missing)' \
        ELSE  lf.Electricity 
    END) AS [Electric Motor Energy Source],
    MAX(tg.TG_Fuel + ', EL') AS [TG Fuel Sources],
    MAX(CASE 
        WHEN l.LabelCalculationApproachIdentifier ='PHEV' THEN  'PHEV'
        ELSE '(Not PHEV)' 
    END) AS [Label Calculation Approach] 
FROM Relational.Label l 
INNER JOIN (
    SELECT 
        lbl.SubmissionId, 
        lbl.ModelTypeIndexNumber, 
        COUNT(*) AS lblFuelCount, 
        MAX(CASE 
                WHEN fu.FuelUsageIdentifier <>'EL' THEN fu.FuelUsageIdentifier 
                ELSE NULL 
            END) AS CE_Fuel, 
        MAX(CASE 
                WHEN fu.FuelUsageIdentifier ='EL' THEN fu.FuelUsageIdentifier 
                ELSE '' 
            END) AS Electricity 
    FROM Relational.Label lbl 
    INNER JOIN Relational.FuelUsage fu 
        ON lbl.LabelId=fu.LabelId 
    WHERE lbl.SubmissionId=@SubmissionId 
    GROUP BY lbl.SubmissionId, lbl.ModelTypeIndexNumber 
    ) AS lf 
    ON l.SubmissionId=lf.SubmissionId 
INNER JOIN (
    SELECT 
        rtg.SubmissionId, 
        rtg.TestGroupName, 
        MAX(CASE 
            WHEN ds.FuelId <> 'EL' THEN ds.FuelId 
            ELSE NULL 
        END) AS TG_Fuel 
    FROM Relational.DriveSource ds 
    INNER JOIN Relational.TestGroup rtg 
        ON ds.TestGroupId=rtg.TestGroupId 
    WHERE rtg.SubmissionId=@SubmissionId 
    GROUP BY rtg.SubmissionId, rtg.TestGroupName 
    HAVING COUNT(*)>1 AND MAX(CASE WHEN ds.FuelId ='EL' THEN 1 ELSE 0 END) = 1
    ) AS tg 
    ON lf.SubmissionId=tg.SubmissionId 
    AND l.TestGroupName = tg.TestGroupName 
WHERE (lf.Electricity = '' AND l.LabelCalculationApproachIdentifier = 'PHEV') 
GROUP BY     
    l.ModelTypeIndexNumber,
    l.TestGroupname