SQL表关联后CASE聚合表达式逻辑异常问题排查与解决

未关联表时，以下SQL中的CASE表达式结合STRING_AGG聚合函数可正常执行：

select t1.PNODE_ID, --t2.NODE_ID,
case
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP26,AS_SP15' then 'SP15'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP15,AS_SP26' then 'SP15'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP26,AS_NP15' then 'NP15'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP15,AS_NP26' then 'NP15'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP15,AS_NP26' then 'ZP26'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP26,AS_SP15' then 'ZP26'
    else 'Unknown'
end as CLEANED_ZONE
from ATL_AS_REGION_MAP t1
--left join ATL_CBNODE t2 on t1.PNODE_ID = t2.NODE_ID
group by 
t1.PNODE_ID
--t2.NODE_ID

但左关联ATL_CBNODE表后，只要存在匹配的NODE_ID，CASE表达式就会进入else分支返回'Unknown'；无匹配时则正常执行。关联后的SQL如下：

select t1.PNODE_ID, STRING_AGG(t1.AS_REGION_ID, ',') as AGG_STRING, t2.NODE_ID,
case
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP26,AS_SP15' then 'SP15'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP15,AS_SP26' then 'SP15'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP26,AS_NP15' then 'NP15'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP15,AS_NP26' then 'NP15'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP15,AS_NP26' then 'ZP26'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP26,AS_SP15' then 'ZP26'
    else 'Unknown'
end as CLEANED_ZONE
from ATL_AS_REGION_MAP t1
left join ATL_CBNODE t2 on t1.PNODE_ID = t2.NODE_ID
group by 
t1.PNODE_ID,
t2.NODE_ID

核心问题出在分组逻辑的变化：

• 未关联表时，仅按t1.PNODE_ID分组，会将同一个PNODE_ID下的所有AS_REGION_ID聚合为完整字符串，能匹配CASE中的预设条件。
• 左关联ATL_CBNODE后，分组字段新增了t2.NODE_ID：
  1. 若t2中存在与t1.PNODE_ID匹配的NODE_ID，且一个PNODE_ID对应多条t2记录，原t1中同一PNODE_ID的记录会被拆分为多个分组（每个分组对应一个t2.NODE_ID）。
  2. 拆分后的每个分组仅包含原t1中部分AS_REGION_ID，导致STRING_AGG生成的字符串不再是CASE中预设的完整组合，因此无法匹配任何条件，进入else分支返回'Unknown'。
  3. 无匹配时t2.NODE_ID为NULL，同一PNODE_ID的所有记录会被分到同一分组（NULL在分组时视为同一组），聚合结果正常，CASE能匹配。

有两种可行方案，可根据业务需求选择：

方案1：先聚合再关联 #

先对t1按PNODE_ID完成聚合和CASE判断，再左关联t2表，避免关联操作影响分组逻辑：

with agg_t1 as (
    select 
        t1.PNODE_ID,
        STRING_AGG(t1.AS_REGION_ID, ',') as AGG_STRING,
        case
            when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP26,AS_SP15' then 'SP15'
            when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP15,AS_SP26' then 'SP15'
            when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP26,AS_NP15' then 'NP15'
            when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP15,AS_NP26' then 'NP15'
            when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP15,AS_NP26' then 'ZP26'
            when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP26,AS_SP15' then 'ZP26'
            else 'Unknown'
        end as CLEANED_ZONE
    from ATL_AS_REGION_MAP t1
    group by t1.PNODE_ID
)
select 
    a.PNODE_ID,
    a.AGG_STRING,
    t2.NODE_ID,
    a.CLEANED_ZONE
from agg_t1 a
left join ATL_CBNODE t2 on a.PNODE_ID = t2.NODE_ID;

方案2：调整分组与t2字段的聚合方式 #

如果需要在关联后直接处理，可仅按t1.PNODE_ID分组，对t2.NODE_ID使用聚合函数（如MAX/MIN，适用于t1.PNODE_ID与t2.NODE_ID为一对一或一对多但只需取一个值的场景）：

select 
    t1.PNODE_ID,
    STRING_AGG(t1.AS_REGION_ID, ',') as AGG_STRING,
    MAX(t2.NODE_ID) as NODE_ID, -- 或MIN，根据业务需求选择
    case
        when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP26,AS_SP15' then 'SP15'
        when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP15,AS_SP26' then 'SP15'
        when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP26,AS_NP15' then 'NP15'
        when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP15,AS_NP26' then 'NP15'
        when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP15,AS_NP26' then 'ZP26'
        when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP26,AS_SP15' then 'ZP26'
        else 'Unknown'
    end as CLEANED_ZONE
from ATL_AS_REGION_MAP t1
left join ATL_CBNODE t2 on t1.PNODE_ID = t2.NODE_ID
group by t1.PNODE_ID;

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