基于其他列值选择对应列：现有SQL实现能否优化？

我现在要从claim_flexfields表里，根据flex_field1_label到flex_field5_label中等于「Member ID」的标签，取出对应的flex_field1_value到flex_field5_value列的值作为Member ID。目前用CASE语句写的代码如下，想问问有没有更高效的实现方式？

select 
claim_id,
case when flex_field1_label = 'Member ID' then flex_field1_value
when flex_field2_label = 'Member ID' then flex_field2_value
when flex_field3_label = 'Member ID' then flex_field3_value
when flex_field4_label = 'Member ID' then flex_field4_value
when flex_field5_label = 'Member ID' then flex_field5_value end as "Member ID"
from claim_flexfields

几种替代方案： #

• 用COALESCE简化写法：如果每行只有一个标签会匹配「Member ID」，可以把CASE改成COALESCE的形式，代码更简洁，执行效率和CASE相当，但可读性更好：

select
  claim_id,
  coalesce(
    case when flex_field1_label = 'Member ID' then flex_field1_value end,
    case when flex_field2_label = 'Member ID' then flex_field2_value end,
    case when flex_field3_label = 'Member ID' then flex_field3_value end,
    case when flex_field4_label = 'Member ID' then flex_field4_value end,
    case when flex_field5_label = 'Member ID' then flex_field5_value end
  ) as "Member ID"
from claim_flexfields

• 用UNPIVOT重构数据结构：如果你的数据库支持UNPIVOT（比如Oracle、SQL Server、PostgreSQL 11+），可以先把宽表转成窄表，再过滤匹配的标签，这种方式在字段更多的时候扩展性更强：

-- 以Oracle为例
select claim_id, value as "Member ID"
from claim_flexfields
unpivot (
  (label, value) for field in (
    (flex_field1_label, flex_field1_value) as 'field1',
    (flex_field2_label, flex_field2_value) as 'field2',
    (flex_field3_label, flex_field3_value) as 'field3',
    (flex_field4_label, flex_field4_value) as 'field4',
    (flex_field5_label, flex_field5_value) as 'field5'
  )
)
where label = 'Member ID';

• 索引优化：如果表数据量很大，给flex_field1_label到flex_field5_label这几个字段建联合索引（或单独索引），能大幅提升匹配效率。

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