如何编写SQL实现查询结果同步展示合并字段的来源表名

实现逻辑 #

完全对齐COALESCE函数的取值优先级，按左连接顺序优先判断上游表对应字段是否为有效值，匹配后返回对应表名即可，该方案兼容所有主流SQL引擎。

完整实现代码 #

select
  COALESCE(t1.col1,t2.col1,t3.col1) new_col1,
  COALESCE(t1.col2,t2.col2,t3.col2) new_col2,
  COALESCE(t1.col3,t2.col3,t3.col3) new_col3,
  -- 新增来源标注字段
  CASE
    WHEN t1.col1 IS NOT NULL THEN 'table1'
    WHEN t2.col1 IS NOT NULL THEN 'table2'
    WHEN t3.col1 IS NOT NULL THEN 'table3'
    ELSE NULL
  END AS new_col1_source,
  CASE
    WHEN t1.col2 IS NOT NULL THEN 'table1'
    WHEN t2.col2 IS NOT NULL THEN 'table2'
    WHEN t3.col2 IS NOT NULL THEN 'table3'
    ELSE NULL
  END AS new_col2_source,
  CASE
    WHEN t1.col3 IS NOT NULL THEN 'table1'
    WHEN t2.col3 IS NOT NULL THEN 'table2'
    WHEN t3.col3 IS NOT NULL THEN 'table3'
    ELSE NULL
  END AS new_col3_source
from
table1 t1 
left join table2 t2 on t1.id = t2.id
left join table3 t3 on t1.id = t3.id

特殊场景适配 #

如果业务规则中空字符串、全空白内容也被视为无效值，需要向后匹配后续表的字段，可调整判断条件，示例如下：

CASE
  WHEN t1.col1 IS NOT NULL AND TRIM(t1.col1) != '' THEN 'table1'
  WHEN t2.col1 IS NOT NULL AND TRIM(t2.col1) != '' THEN 'table2'
  WHEN t3.col1 IS NOT NULL AND TRIM(t3.col1) != '' THEN 'table3'
  ELSE '无有效来源'
END AS new_col1_source

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