使用Case与Select Distinct转置行列时返回行数超出预期

问题分析 #

你的核心需求是将按Type拆分的能耗数据（Use/Cost）转成列，且每个ID+Month+Year组合仅保留一行。原SQL的问题在于：

• 内层GROUP BY包含了Metric_Type、Use、Cost、seq，这会让每个Type的行（包括NULL值行和有数据行）单独成为分组，导致最终结果出现重复的ID+Month+Year行
• DISTINCT无法解决这个问题，因为分组后的行本身在Electricity_Use等字段上有差异（一个是NULL，一个是有效值）

正确的SQL查询 #

直接按ID、Year、Month分组，利用聚合函数（MAX）将不同Type的Use和Cost转成对应列：

SELECT 
    ID,
    [Year],
    [Month],
    MAX(CASE WHEN Type = 'Electricity' THEN [Use] END) AS [Electricity Use],
    MAX(CASE WHEN Type = 'Electricity' THEN [Cost] END) AS [Electricity Cost],
    MAX(CASE WHEN Type = 'Natural Gas' THEN [Use] END) AS [Natural Gas Use],
    MAX(CASE WHEN Type = 'Natural Gas' THEN [Cost] END) AS [Natural Gas Cost],
    MAX(CASE WHEN Type = 'Water' THEN [Use] END) AS [Water Use],
    MAX(CASE WHEN Type = 'Water' THEN [Cost] END) AS [Water Cost],
    MAX(CASE WHEN Type = 'Diesel' THEN [Use] END) AS [Diesel Use],
    MAX(CASE WHEN Type = 'Diesel' THEN [Cost] END) AS [Diesel Cost],
    MAX(CASE WHEN Type = 'Gasoline' THEN [Use] END) AS [Gasoline Use],
    MAX(CASE WHEN Type = 'Gasoline' THEN [Cost] END) AS [Gasoline Cost],
    MAX(CASE WHEN Type = 'Propane' THEN [Use] END) AS [Propane Use],
    MAX(CASE WHEN Type = 'Propane' THEN [Cost] END) AS [Propane Cost]
FROM 
    dbo.Usage
GROUP BY 
    ID, [Year], [Month]
ORDER BY 
    ID, 
    [Year] DESC, 
    DATEPART(mm, CAST([Month] + ' 1900' AS DATETIME)) ASC

方案说明 #

• 仅按ID、Year、Month分组，确保每个组合只生成一行
• MAX(CASE...)会自动忽略NULL值，保留对应Type下的有效数值（如果存在）；如果没有有效值，则返回NULL，完全匹配你期望的输出格式
• 去掉了冗余的子查询和DENSE_RANK，逻辑更简洁，性能更优

执行上述SQL后，April 2022的行只会保留一行，其中Electricity Use为13，Electricity Cost为44，其余对应字段为NULL，符合需求。

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