时间戳

首页 > 解决方案 > Postgres - 在表中的列中搜索特定值

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

问题描述

我有一张表格，列出了客户在商店购买的所有水果：

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

我正在尝试创建一个输出，其中显示apples了上表中标记的客户购买了哪些水果（水果 1/水果 2、水果 3）。我知道case语句只能应用于单个列，所以我想知道是否有办法让已购买的客户apples。

预期输出：

cust_a,fruit1
cust_b,
cust_c,fruit3

标签： sqlpostgresql

解决方案

一种无需编写复杂的 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

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

推荐阅读