SQL中如何为COALESCE生成的the_id添加条件前缀以区分值来源

直接用CASE条件判断替换原有COALESCE逻辑即可，通过判断grouped_wells.groupforecasting_id是否非空，拼接对应前缀，就能避免数值相同的不同来源ID被分到同一组。

核心修改逻辑 #

the_id字段的生成逻辑替换为：

CASE
    WHEN grouped_wells.groupforecasting_id IS NOT NULL THEN CONCAT('group_', grouped_wells.groupforecasting_id)
    ELSE CONCAT('well_', wells.id)
END AS the_id

修改后完整SQL #

SELECT 
    CASE
        WHEN grouped_wells.groupforecasting_id IS NOT NULL THEN CONCAT('group_', grouped_wells.groupforecasting_id)
        ELSE CONCAT('well_', wells.id)
    END AS the_id,
    string_agg(wells.name,', ') as well_name,
    sum(gas_cd) as gas_cd,
    date 
FROM productions
INNER JOIN completions on completions.id = productions.completion_id 
INNER JOIN wellbores on wellbores.id = completions.wellbore_id 
INNER JOIN wells on wells.id = wellbores.well_id 
INNER JOIN fields on fields.id = wells.field_id 
INNER JOIN clusters on clusters.id = fields.cluster_id
LEFT JOIN grouped_wells on grouped_wells.wells_id = wells.id
LEFT JOIN groupforecasting on groupforecasting.id = grouped_wells.groupforecasting_id and groupforecasting.workspace_id = 3
GROUP BY the_id, productions.date
ORDER BY the_id, productions.date

兼容说明 #

如果你使用Oracle数据库，拼接语法需要调整为双竖线写法：

CASE
    WHEN grouped_wells.groupforecasting_id IS NOT NULL THEN 'group_' || grouped_wells.groupforecasting_id
    ELSE 'well_' || wells.id
END AS the_id

内容的提问来源于stack exchange，提问作者Sami Al-Subhi