如何用单条SQL语句按pool_id筛选符合多规则的关联Items？

数据表结构 #

Items表 #

Categories表 #

Tags表 #

Tags_items表（Item与Tag关联表） #

Pool表（规则集合） #

Poolrule表（具体规则） #

业务逻辑与规则 #

• Item与Category为一对多关系（一个Item属于一个Category），一个Item可关联多个Tag。
• Pool是规则集合，通过类别、标签的包含/排除逻辑对Item分组。

示例规则 #

• Good items（pool_id=1）：筛选属于x、y类别（包含），且拥有Tag1（包含）、未拥有Tag2（排除）的所有Item。
• Nice items（pool_id=2）：筛选不属于z、a类别（排除），且未拥有Tag3（排除）的所有Item。

规则约束 #

1. 每个Pool的类别规则只能统一为全包含或全排除（Item仅属于一个Category，同时包含+排除类别无意义）。
2. Poolrule中cat_id与tag_id不能同时非空，必有一个为null。
3. 若Pool要求包含多个Tag，Item必须拥有所有指定Tag，可额外拥有其他Tag。
4. 若Pool存在至少一个标签包含规则，则无标签的Item会被排除。

问题与解决方案 #

当前方案先通过WHERE category_id [NOT] IN(...)预筛选Item，再在客户端做标签过滤，缺陷是可能传输大量不符合最终规则的数据。以下是单条SQL实现按指定pool_id筛选符合规则Item的方案：

核心SQL语句（PostgreSQL兼容） #

SELECT DISTINCT i.*
FROM Items i
-- 聚合当前Pool的类别规则
JOIN (
    SELECT 
        pool_id,
        MAX(CASE WHEN exclude = false THEN 'INCLUDE' ELSE 'EXCLUDE' END) AS cat_rule_type,
        ARRAY_AGG(cat_id) AS cat_ids
    FROM Poolrule
    WHERE pool_id = ? -- 替换为目标pool_id
        AND cat_id IS NOT NULL
    GROUP BY pool_id
) cat_rules ON 1=1
-- 聚合当前Pool的标签规则
JOIN (
    SELECT 
        pool_id,
        ARRAY_AGG(CASE WHEN exclude = false THEN tag_id END) FILTER (WHERE exclude = false) AS required_tags,
        ARRAY_AGG(CASE WHEN exclude = true THEN tag_id END) FILTER (WHERE exclude = true) AS excluded_tags,
        BOOL_OR(exclude = false) AS has_required_tags
    FROM Poolrule
    WHERE pool_id = ? -- 替换为目标pool_id
        AND tag_id IS NOT NULL
    GROUP BY pool_id
) tag_rules ON 1=1
-- 类别规则过滤
WHERE (
    cat_rules.cat_rule_type = 'INCLUDE' AND i.category_id = ANY(cat_rules.cat_ids)
    OR
    cat_rules.cat_rule_type = 'EXCLUDE' AND i.category_id <> ALL(cat_rules.cat_ids)
)
-- 标签规则过滤
AND (
    -- 无标签规则时直接通过
    (tag_rules.required_tags IS NULL AND tag_rules.excluded_tags IS NULL)
    OR
    (
        -- 处理必填标签：Item需拥有所有指定标签
        (tag_rules.has_required_tags = false OR EXISTS (
            SELECT 1
            FROM Tags_items ti
            WHERE ti.item_id = i.id
            GROUP BY ti.item_id
            HAVING ARRAY_AGG(ti.tag_id) @> tag_rules.required_tags
        ))
        -- 处理排除标签：Item不能拥有任何指定标签
        AND NOT EXISTS (
            SELECT 1
            FROM Tags_items ti
            WHERE ti.item_id = i.id
                AND ti.tag_id = ANY(tag_rules.excluded_tags)
        )
        -- 有必填标签时，排除无标签的Item
        AND (tag_rules.has_required_tags = false OR EXISTS (SELECT 1 FROM Tags_items ti WHERE ti.item_id = i.id))
    )
);

方案说明 #

1. 类别规则处理：通过子查询聚合当前Pool的类别规则类型（包含/排除）和类别ID列表，用= ANY/<> ALL实现批量匹配。
2. 标签规则处理：
   • 用数组包含运算符@>判断Item是否拥有所有必填标签。
   • 用NOT EXISTS确保Item不包含任何排除标签。
   • 若存在必填标签，通过EXISTS排除无标签的Item。
3. 去重：用DISTINCT避免Item因关联多个标签而重复出现。

跨数据库适配 #

• MySQL：替换数组函数为GROUP_CONCAT，用FIND_IN_SET或关联子查询判断标签关系。
• SQL Server：改用STRING_AGG和STRING_SPLIT，或表值函数处理标签集合。

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