sql - 多案例声明无法在Postgres中正常工作

这是一个查询：

SELECT DISTINCT
         completed_phases,
         CAST(completed_phases::bit(8) AS VARCHAR),
         CASE WHEN STRPOS(CAST(completed_phases::bit(8) AS VARCHAR),'1')=1 THEN 'FT' ELSE '' END ||
         CASE WHEN STRPOS(CAST(completed_phases::bit(8) AS VARCHAR),'1')=2 THEN 'ED' ELSE '' END ||
         CASE WHEN STRPOS(CAST(completed_phases::bit(8) AS VARCHAR),'1')=3 THEN 'MC' ELSE '' END ||
         CASE WHEN STRPOS(CAST(completed_phases::bit(8) AS VARCHAR),'1')=4 THEN 'HC' ELSE '' END ||
         CASE WHEN STRPOS(CAST(completed_phases::bit(8) AS VARCHAR),'1')=5 THEN 'UV' ELSE '' END ||
         CASE WHEN STRPOS(CAST(completed_phases::bit(8) AS VARCHAR),'1')=6 THEN 'TT' ELSE '' END ||
         CASE WHEN STRPOS(CAST(completed_phases::bit(8) AS VARCHAR),'1')=7 THEN 'RX' ELSE '' END ||
         CASE WHEN STRPOS(CAST(completed_phases::bit(8) AS VARCHAR),'1')=8 THEN 'PI' ELSE '' END
FROM rx_sales_order

如果completed_phase是129，那么最后一列的输出应该是FTPI。但这只显示了英国《金融时报》，只有第一个案例陈述似乎起作用，即使它们都是不同的。

最佳答案

STRPOS()将始终返回搜索字符串的第一次出现。所以对strpos()的所有调用都将返回1作为输入值129。
您可以使用substring()代替：

CASE WHEN substring(CAST(completed_phases::bit(8) AS VARCHAR),1,1)='1' THEN 'FT' ELSE '' END ||
CASE WHEN substring(CAST(completed_phases::bit(8) AS VARCHAR),2,1)='1' THEN 'ED' ELSE '' END ||
CASE WHEN substring(CAST(completed_phases::bit(8) AS VARCHAR),3,1)='1' THEN 'MC' ELSE '' END ||
CASE WHEN substring(CAST(completed_phases::bit(8) AS VARCHAR),4,1)='1' THEN 'HC' ELSE '' END ||
CASE WHEN substring(CAST(completed_phases::bit(8) AS VARCHAR),5,1)='1' THEN 'UV' ELSE '' END ||
CASE WHEN substring(CAST(completed_phases::bit(8) AS VARCHAR),6,1)='1' THEN 'TT' ELSE '' END ||
CASE WHEN substring(CAST(completed_phases::bit(8) AS VARCHAR),7,1)='1' THEN 'RX' ELSE '' END ||
CASE WHEN substring(CAST(completed_phases::bit(8) AS VARCHAR),8,1)='1' THEN 'PI' ELSE '' END

另一种选择是使用get_bit()单独测试每个位：

case when get_bit(completed_phases::bit(8), 0) = 1 then 'FT' else '' END||
case when get_bit(completed_phases::bit(8), 1) = 1 then 'ED' else '' END||
case when get_bit(completed_phases::bit(8), 2) = 1 then 'MC' else '' END||
case when get_bit(completed_phases::bit(8), 3) = 1 then 'HC' else '' END||
case when get_bit(completed_phases::bit(8), 4) = 1 then 'UV' else '' END||
case when get_bit(completed_phases::bit(8), 5) = 1 then 'TT' else '' END||
case when get_bit(completed_phases::bit(8), 6) = 1 then 'RX' else '' END||
case when get_bit(completed_phases::bit(8), 7) = 1 then 'PI' else '' END

一种更灵活的方式是将这些位转换成行，并使用数组作为查找。类似于：

with lookup (codes) as (
   values (array['FT','ED','MC','HC','UV','TT','RX','PI'])
)
SELECT  completed_phases,
        completed_phases::bit(8),
        x.code
FROM rx_sales_order
  join lateral (
    select string_agg(codes[i],'') as code
    from lookup, unnest(string_to_array(completed_phases::bit(8)::text, null)) with ordinality as t(b,i)
    where b = '1'
 ) as x on true

部分regexp_split_to_table(completed_phases::bit(8)::text, '') with ordinality as t(b,i)将返回值129的以下值：

b | i
--+--
1 | 1
0 | 2
0 | 3
0 | 4
0 | 5
0 | 6
0 | 7
1 | 8

code[i]使用索引查找匹配的代码，然后再次将所有选定的代码放在一个字符串中。条件string_agg()仅选择已设置的位。
该解决方案将比硬编码的where b = '1'表达式慢得多（因为它增加了行数，只是为了再次减少它们）——但是它更灵活，更容易维护。
如果您非常需要，最好的选择是将case表达式放入一个函数并在查询中使用该函数。

create or replace function get_codes(p_phases integer)
  returns text
as
$$
  select
    case when get_bit(p_phases::bit(8), 0) = 1 then 'FT' else '' END||
    case when get_bit(p_phases::bit(8), 1) = 1 then 'ED' else '' END||
    case when get_bit(p_phases::bit(8), 2) = 1 then 'MC' else '' END||
    case when get_bit(p_phases::bit(8), 3) = 1 then 'HC' else '' END||
    case when get_bit(p_phases::bit(8), 4) = 1 then 'UV' else '' END||
    case when get_bit(p_phases::bit(8), 5) = 1 then 'TT' else '' END||
    case when get_bit(p_phases::bit(8), 6) = 1 then 'RX' else '' END||
    case when get_bit(p_phases::bit(8), 7) = 1 then 'PI' else '' END
$$
language sql;

然后使用：

SELECT DISTINCT
         completed_phases,
         get_codes(completed_phases) as codes
FROM rx_sales_order