如何通过关联表检查包含指定有序商品的重复集合？

好的，我来帮你解决这个问题。你的需求是检查是否存在一个完全包含指定商品且顺序完全一致的自定义集合，对吧？结合你的三张表结构（collections、items、collection_items），我给你两种可靠的SQL查询方案，都能精准解决这个问题。

方案一：序列匹配法（严谨可靠，适合复杂场景） #

这个方法通过构建目标商品的期望序列，逐一匹配集合中的商品及其顺序，同时确保集合没有额外商品。

假设你要检查的目标商品序列是：item_id按顺序为 2 → 4 → 6（对应期望的order值为1、2、3），具体SQL如下：

WITH target_sequence AS (
    -- 这里定义你的目标商品序列：期望顺序 + 对应的item_id
    SELECT expected_order, item_id
    FROM (
        VALUES 
            (1, 2),
            (2, 4),
            (3, 6)
    ) AS seq(expected_order, item_id)
)
SELECT c.id, c.name
FROM collections c
JOIN collection_items ci ON c.id = ci.collection_id
JOIN target_sequence ts ON ci.item_id = ts.item_id
GROUP BY c.id, c.name
HAVING 
    -- 条件1：集合包含所有目标商品，无遗漏
    COUNT(DISTINCT ci.item_id) = (SELECT COUNT(*) FROM target_sequence)
    -- 条件2：每个商品的实际order与期望顺序完全匹配
    AND SUM(CASE WHEN ci.order = ts.expected_order THEN 1 ELSE 0 END) = (SELECT COUNT(*) FROM target_sequence)
    -- 条件3：集合没有额外商品，数量与目标序列一致
    AND (SELECT COUNT(*) FROM collection_items WHERE collection_id = c.id) = (SELECT COUNT(*) FROM target_sequence);

关键逻辑说明： #

• target_sequence CTE 用来定义你要检查的商品顺序和对应的item_id
• 三个HAVING条件共同确保：集合不多不少正好包含所有目标商品，且每个商品的顺序完全符合预期

方案二：字符串拼接法（简洁直观，适合简单场景） #

这个方法把每个集合的item_id按order排序后拼接成字符串，直接和目标序列的拼接字符串对比，同时验证商品数量一致。

同样以目标序列2 → 4 → 6为例：

-- 适用于PostgreSQL
WITH target AS (
    SELECT '2|4|6' AS target_string, 3 AS target_count
)
SELECT c.id, c.name
FROM collections c
JOIN (
    SELECT 
        collection_id,
        STRING_AGG(item_id::TEXT, '|' ORDER BY "order") AS item_sequence,
        COUNT(*) AS item_count
    FROM collection_items
    GROUP BY collection_id
) ci ON c.id = ci.collection_id
JOIN target t ON ci.item_sequence = t.target_string AND ci.item_count = t.target_count;

注意事项： #

• 如果使用MySQL，把STRING_AGG替换为GROUP_CONCAT：

  GROUP_CONCAT(item_id ORDER BY `order` SEPARATOR '|') AS item_sequence
  

• 分隔符（比如这里的|）要选择不会出现在item_id中的字符，避免拼接后出现匹配错误
• order是SQL关键字，查询时要用反引号（MySQL）或双引号（PostgreSQL）包裹

结果判断： #

如果上述任意一个查询返回了记录，说明已经存在完全匹配的集合；如果没有返回记录，就可以安全创建新的自定义集合了。

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