多个字段的 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 不熟悉，但也许您可以尝试使用通用表表达式来构建一个表，该表索引您的查找条件以及组和子组的输出结果，它可能看起来像这样（当然条件可能来自实际的table as well...):

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”的解决方案。

此致，