sql - 根据相同的条件语句 SQL 选择两列

Question

我有一个类似这样的选择语句

SELECT
    CASE WHEN <SOME-CONDN_1> THEN 'value1' ELSE '' ||
    CASE WHEN <SOME-CONDN_2> THEN 'value2' ELSE '' ||
    CASE WHEN <SOME-CONDN_3> THEN 'value3' ELSE '' AS value_column,
    CASE WHEN <SOME-CONDN_1> THEN 'name1' ELSE '' ||
    CASE WHEN <SOME-CONDN_2> THEN 'name2' ELSE '' ||
    CASE WHEN <SOME-CONDN_3> THEN 'name3' ELSE '' AS name_column
FROM data_table
--<REST OF THE QUERY>

条件语句类似于data_table.data_column ILIKE value1 等等。由于我两次执行相同的条件语句(并且它涉及一些使用 ILIKE 的字符串匹配)，我想知道我是否可以将它们合并并提高效率。

使用 SQL 语句也可以吗？

Answer 1

选项 1:不太确定这种 CASE 变体是否适用于 PostgreSQL...

select case cond_num when 
    1 then 'value1'
    when 2 then 'value2',
    when 3 then 'value3' else null end as value_column,
case cond_num when 
    1 then 'name1'
    when 2 then 'name2',
    when 3 then 'name3' else null end as name_column
from (
select data_table.*,
    case when <some_condition_1> then 1 
    when <some_condition_2> then 2
    when <some_condition_3> then 3 else 0 end as cond_num
from data_table
) screened_table
;

选项 2:

select case when 
    cond1 = 1 then 'value1'
    when cond2 = 1 then 'value2',
    when cond3 = 1 then 'value3' else null end as value_column,
case when 
    cond1 = 1 then 'name1'
    when cond2 = 1 then 'name2',
    when cond3 = 1 then 'name3' else null end as name_column
from (
select data_table.*,
    case when <some_condition_1> then 1 else 0 as cond1,
    case when <some_condition_2> then 1 else 0 as cond2,
    case when <some_condition_3> then 1 else 0 as cond3
from data_table
) screened_table
;

选项 3 - 注意如果条件不排他可能会返回多行。不会从 data_table 返回没有条件为真的行。

select rslt.name, rslt.value
from data_table, (
    select 1 as cond, 'value1' as value, 'name1' as name
    union all
    select 2 as cond, 'value2' as value, 'name2' as name
    union all
    select 3 as cond, 'value3' as value, 'name3' as name
) rslt
WHERE (<some_condition_1> and rslt.cond = 1) OR
    (<some_condition_2> and rslt.cond = 2) OR
    (<some_condition_3> and rslt.cond = 3)

;