sql - Postgres - 在表中跨列搜索特定值

Question

我有一个表格，其中列出了顾客在一家商店购买的所有水果:

| cust_name | fruit1 | fruit2  | fruit3 |
|-----------|--------|---------|--------|
| cust_a    | apples | oranges | pears  |
| cust_b    | pears  |         |        |
| cust_c    |        |         | apples |

我正在尝试创建一个输出，其中显示上表中标记为 apples 的客户购买了哪种水果(fruit1/fruit2，fruit3)。我知道 case 语句只能应用于单个列，所以我想知道是否有办法获得购买了 apples 的客户。

预期输出:

cust_a,fruit1
cust_b,
cust_c,fruit3

Answer 1

无需编写复杂的 WHERE 子句并且可以轻松扩展到更多列的一种方法是将行转换为 JSON 并迭代生成的 JSON 值的键:

select t.cust_name, string_agg(r.field, ',')
from the_table t
  left join lateral jsonb_each_text(to_jsonb(t) - 'cust_name') as r(field, fruit) 
                 on r.fruit = 'apples'
group by t.cust_name;

to_jsonb(t) - 'cust_name' 使用行中的所有列创建一个 JSON 值并删除 cust_name。从 JSON 中删除 cust_name 并不是绝对必要的，因为它不太可能包含水果名称，所以它永远不会被返回。

jsonb_each_text() 然后“迭代”所有列并仅保留包含值 apples 的列，然后将结果聚合回以逗号分隔的列表以防万一。

使用以下示例数据:

create table the_table (cust_name text, fruit1 text, fruit2 text, fruit3 text)
insert into the_table
values 
  ('cust_a', 'apples', 'oranges', 'pears'),
  ('cust_b', 'pears', null, null),
  ('cust_c', null,  null, 'apples'),
  ('cust_d', 'apples',  null, 'apples');

上面的查询返回:

cust_name | string_agg   
----------+--------------
cust_a    | fruit1       
cust_b    |              
cust_c    | fruit3       
cust_d    | fruit1,fruit3

不过，正确规范化数据模型会是一个更好的解决方案。