我有一张桌子，看起来像这样:
| id | colors | other column | other column |
|---|---|---|---|
| 1 | red, green, blue | ... | ... |
| 2 | red, blue | ... | ... |
| 3 | red | ... | ... |
| 4 | white, blue, purple | ... | ... |
| 5 | green, white, purple | ... | ... |
我需要一个SQL查询，它将返回值与我的过滤器列表中的值匹配only的行，而不返回在Colors列中有任何其他值的行.
我正在处理的查询如下:
select * from table where colors in ('red', 'blue');
这个函数的问题在于，它将在结果中返回第1-4行，因为每一行的 colored颜色 都是红色或蓝色.相反，我只想返回第2行和第3行，这是我的滤镜列表之外唯一没有任何额外 colored颜色 的行.
列中可能有任意数量的 colored颜色 ，所以很遗憾我没有一个很好的列表来用作NOT滤镜.我需要能够从数据库返回整个行，包括所有可用列.
感谢任何帮助或建议.
蒂娅！
正如其他人在 comments 区指出的那样，这真的是一个糟糕的设计，会在future 造成更多的痛苦，如果可能的话，请修复它.
见Is storing a delimited list in a database column really that bad?
关于这个问题，
首先拆分逗号分隔的字符串
SELECT
id,
TRIM(UNNEST(STRING_TO_ARRAY(colors, ','))) AS colors
FROM my_table
结果
id colors
1 red
1 green
1 blue
2 red
2 blue
3 red
......
然后过滤结果
with cte as (
SELECT id,
TRIM(UNNEST(STRING_TO_ARRAY(colors, ','))) AS colors
FROM my_table
) select id
from cte
group by id
having count (distinct colors) = 2
and min(colors) = 'blue'
and max(colors) = 'red';
结果
id
2
如果您需要使用所有的柱，
with split_string as (
SELECT id,
TRIM(UNNEST(STRING_TO_ARRAY(colors, ','))) AS colors
FROM my_table
), ids as (
select id
from split_string
group by id
having count (distinct colors) = 2
and min(colors) = 'blue'
and max(colors) = 'red'
) select m.*
from my_table m
inner join ids i on i.id=m.id;
结果
id colors
2 red, blue
请参见示例here