sql - 多个字段的 CASE 语句结果

Question

运行 PostgreSQL 7.x(是的，我正在升级)

例子:

SELECT
    CASE 
        WHEN "substring"(t."Field"::text, 0, 3) = '01'::text THEN 'group one'::text
        WHEN "substring"(t."Field"::text, 0, 4) = '123'::text THEN 'group one'::text
        WHEN "substring"(t."Field"::text, 0, 5) = '4567'::text THEN 'group two'::text
        WHEN "substring"(t."Field"::text, 0, 6) = '99999'::text THEN 'group three'::text
        WHEN "substring"(t."Field"::text, 0, 3) = '07'::text THEN 'group three'::text
        ELSE NULL::text
    END AS new_field,
    CASE 
        WHEN "substring"(t."Field"::text, 0, 3) = '01'::text THEN 'subgroup a'::text
        WHEN "substring"(t."Field"::text, 0, 4) = '123'::text THEN 'subgroup a'::text
        WHEN "substring"(t."Field"::text, 0, 5) = '4567'::text THEN 'subgroup a'::text
        WHEN "substring"(t."Field"::text, 0, 6) = '99999'::text THEN 'subgroup a'::text
        WHEN "substring"(t."Field"::text, 0, 3) = '07'::text THEN 'subgroup b'::text
        ELSE NULL::text
    END AS another_new_field,...

有没有办法让一个 case 语句给出两个字段作为结果，因为处理的数据是相同的，只是标签不同。

Answer 1

我对 Postgre 不熟悉，但也许你可以尝试一个公共(public)表表达式来构建一个表来索引你的查找条件以及组和子组的输出结果，它可能看起来像这样(当然条件可能来自也来自实际的表......):

with Lookup as (
    select 0 as start, 3 as end,
        '01' as match, 'group one' as sgroup, 'subgroup a' as subgroup union
    select 0 as start, 4 as end,
        '123' as match, 'group one' as sgroup, 'subgroup a' as subgroup union
    select 0 as start, 5 as end,
        '4567' as match, 'group two' as sgroup, 'subgroup a' as subgroup union
    select 0 as start, 6 as end,
        '99999' as match, 'group three' as sgroup, 'subgroup a' as subgroup union
    select 0 as start, 3 as end,
        '07' as match, 'group three' as sgroup, 'subgroup b' as subgroup union
)
select
    L.sgroup as new_field,
    L.subgroup as another_new_field
from table T
left join Lookup L on "substring"(T."Field"::text, L.start, L.End) = L.match

那么你只需要定义一次条件，并匹配一次它们就可以输出两个字段。正如@Tom 在评论中所表达的那样，这种情况在 SQL 中处理得不好，因此您只能使用与上述类似的更“hacky”的解决方案。

最好的问候，