sql - 从列中的逗号分隔值搜索

Question

我有一个包含 2 列的表 - id 和 pets。

宠物列包含由 , [逗号] 分隔的缩写宠物名称，如下所示

+----+-------------+
| id | pets        |
+----+-------------+
| 1  | CAT,DOG     |
+----+-------------+
| 2  | CAT,DOG,TIG |
+----+-------------+
| 3  | ZEB,MOU     |
+----+-------------+

现在我想列出所有 id's where pets = CAT，类似地所有 id's where pets = DOG 等

我最初的尝试是为每只宠物(CAT、DOG 等)运行以下 SQL

select id
from "favpets"
where pets like '%CAT%'

这个简单解决方案的局限性在于实际的表并没有。养宠物并不像上面说的那么简单。

没有。这样的宠物有200多个，所以需要执行200条sql才能列出所有宠物对应的id

有什么好的替代方案吗？我正在使用 doctrine，那么 doctrine 是否提供了任何好的实现？

Answer 1

通过此查询，您将获得所有 id，所有宠物，按宠物排序:

SELECT id, unnest(string_to_array(pets, ',')) AS mypet
FROM favpets
ORDER BY mypet;

将它用作子查询将变得容易分组和计数:

SELECT mypet, COUNT(*) FROM
(
    SELECT id, unnest(string_to_array(pets, ',')) AS mypet
    FROM favpets
    ORDER BY mypet
) AS a
GROUP BY mypet;