如何从ThingCategory表筛选属于指定类别集及更多类别的thing_id？

针对ThingCategory多对多关联表，要查询所有包含给定类别列表（比如(1, 2, 6, 23)）中所有类别的thing_id，可以用GROUP BY结合HAVING的写法，比嵌套子查询更简洁通用：

SELECT thing_id
FROM ThingCategory
WHERE category_id IN (1, 2, 6, 23)
GROUP BY thing_id
HAVING COUNT(DISTINCT category_id) = 4; -- 4是给定类别列表的长度

核心逻辑说明： #

• WHERE category_id IN (...)：过滤出属于目标类别的关联记录
• GROUP BY thing_id：按事物ID分组，聚合其关联的类别数据
• HAVING COUNT(DISTINCT category_id) = N：确保该事物关联的目标类别数量等于给定列表的长度N——只要满足这个条件，无论事物是否关联其他类别，都说明它包含了所有指定类别，符合需求

如果要让查询更通用，避免手动计算类别数量，可以用子查询动态获取目标类别总数：

WITH target_categories AS (
    SELECT UNNEST(ARRAY[1, 2, 6, 23]) AS category_id -- PostgreSQL 写法
)
SELECT tc.thing_id
FROM ThingCategory tc
JOIN target_categories tcats ON tc.category_id = tcats.category_id
GROUP BY tc.thing_id
HAVING COUNT(DISTINCT tc.category_id) = (SELECT COUNT(*) FROM target_categories);

不同数据库适配提示： #

• MySQL：可以用临时表构造目标类别，比如SELECT 1 AS category_id UNION SELECT 2 UNION SELECT 6 UNION SELECT 23替代CTE
• SQL Server：用VALUES (1), (2), (6), (23)来生成目标类别集合

这种写法逻辑清晰，性能通常优于多层嵌套子查询，配合thing_id和category_id的联合索引能大幅提升大表查询效率。

内容的提问来源于stack exchange，提问作者Bernd Wechner