如何在CASE语句中处理多非空列值以执行计算（避免UNION）

现有数据表：

id  lg0 lg7 lg30
1   0   1   0
1   1   0   1
2   0   1   0
2   1   0   1

期望得到结果：

id  window  value
1   0       1
1   7       0
1   30      0
2   0       0
2   7       1
2   30      0
3   0       0
3   7       0
3   30      1

规则说明：

• window列对应原表字段：lg0对应0，lg7对应7，lg30对应30
• 原表所有字段均非空，之前用CASE语句查询时，因优先匹配lg0 IS NOT NULL，无法得到预期结果；要求避免使用UNION实现优化。

原错误查询：

select id,
case 
    when lg0 IS NOT NULL
    then 0
    when lg7 IS NOT NULL
    then 7
    when lg30 IS NOT NULL
    then 30
end as window
,case 
    when lg0 IS NOT NULL
    then lg0
    when lg7 IS NOT NULL
    then lg7 
    when lg30 IS NOT NULL
    then lg30
end as value
from table

核心思路是通过横向连接生成行的方式，把原表每行的3个字段拆分为3行，性能优于UNION拼接。

1. PostgreSQL 写法 #

用UNNEST展开数组实现：

select 
    t.id,
    unnest(array[0,7,30]) as "window",
    unnest(array[t.lg0, t.lg7, t.lg30]) as value
from your_table t

若需处理原表同id多行的情况，可聚合去重：

select 
    t.id,
    unnest(array[0,7,30]) as "window",
    max(unnest(array[t.lg0, t.lg7, t.lg30])) as value
from your_table t
group by t.id, unnest(array[0,7,30])

2. MySQL 8.0+/MariaDB 写法 #

用CROSS JOIN VALUES生成目标行：

select 
    t.id,
    v."window",
    case v."window"
        when 0 then t.lg0
        when 7 then t.lg7
        when 30 then t.lg30
    end as value
from your_table t
cross join (
    values (0), (7), (30)
) as v("window")

聚合去重版本：

select 
    t.id,
    v."window",
    max(case v."window"
        when 0 then t.lg0
        when 7 then t.lg7
        when 30 then t.lg30
    end) as value
from your_table t
cross join (
    values (0), (7), (30)
) as v("window")
group by t.id, v."window"

3. SQL Server 写法 #

用CROSS JOIN (VALUES)生成目标行：

select 
    t.id,
    v.[window],
    case v.[window]
        when 0 then t.lg0
        when 7 then t.lg7
        when 30 then t.lg30
    end as value
from your_table t
cross join (
    values (0), (7), (30)
) as v([window])

聚合去重版本：

select 
    t.id,
    v.[window],
    max(case v.[window]
        when 0 then t.lg0
        when 7 then t.lg7
        when 30 then t.lg30
    end) as value
from your_table t
cross join (
    values (0), (7), (30)
) as v([window])
group by t.id, v.[window]

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