多CASE表达式导致ConsolID行重复的SQL查询优化需求

你的问题核心是一对多关联导致的行膨胀：自定义字段表（存储XV_Name/XV_Data）和主表是一对多关系，直接关联会让每个自定义字段生成独立行。DISTINCT无法解决的原因是：不同行的CASE表达式会返回不同的非NULL值（比如一行返回Master Booking数据，另一行返回Planning Note数据），DISTINCT会保留这些差异行；同时如果HeaderCount是聚合计算值，DISTINCT与未聚合字段混用会违反SQL分组规则，导致报错。

以下是两种可靠的解决方法：

方法1：聚合函数+CASE表达式（通用所有SQL方言） #

通过GROUP BY ConsolID将同一ID的行分组，用MAX()/MIN()聚合函数提取每个自定义字段的有效值（NULL会被聚合函数忽略）：

SELECT 
    c.ConsolID,
    -- 每个自定义字段对应一个CASE+聚合
    MAX(CASE WHEN xv.XV_Name = 'Master Booking' THEN xv.XV_Data END) AS MasterBooking,
    MAX(CASE WHEN xv.XV_Name = 'Planning Note' THEN xv.XV_Data END) AS PlanningNote,
    -- 继续添加其他需要的XV_Name判断
    -- 处理HeaderExists：用MAX聚合HeaderCount确保分组后的值正确
    CASE WHEN ISNULL(MAX(c.HeaderCount), 0) = 0 THEN 'N' ELSE 'Y' END AS HeaderExists
FROM 
    -- 替换为你的主表名
    YourMainTable c
LEFT JOIN 
    -- 替换为自定义字段表名，确保关联键正确（假设是ConsolID）
    YourCustomFieldsTable xv ON c.ConsolID = xv.ConsolID
GROUP BY 
    c.ConsolID;

方法2：PIVOT语法（适用于SQL Server、Oracle等支持的数据库） #

如果你的数据库支持PIVOT，可以更简洁地实现行转列：

SELECT 
    ConsolID,
    [Master Booking] AS MasterBooking,
    [Planning Note] AS PlanningNote,
    -- 列出其他需要转成列的XV_Name
    CASE WHEN ISNULL(HeaderCount, 0) = 0 THEN 'N' ELSE 'Y' END AS HeaderExists
FROM (
    -- 子查询先关联主表和自定义字段表
    SELECT 
        c.ConsolID,
        xv.XV_Name,
        xv.XV_Data,
        c.HeaderCount
    FROM 
        YourMainTable c
    LEFT JOIN 
        YourCustomFieldsTable xv ON c.ConsolID = xv.ConsolID
) AS SourceData
PIVOT (
    -- 用MAX聚合取每个XV_Name对应的XV_Data
    MAX(XV_Data)
    -- 指定要转成列的XV_Name值
    FOR XV_Name IN ([Master Booking], [Planning Note])
) AS PivotResult
-- 确保同一ConsolID只返回一行
GROUP BY 
    ConsolID, HeaderCount;

关键说明 #

• 两种方法都通过分组+聚合实现行转列，确保每个ConsolID只输出一行。
• 如果自定义字段数量固定（你提到最多19个），直接按上述方式逐个添加即可；如果字段不固定，可能需要动态SQL，但固定字段场景下上述方法足够稳定。

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