list - 在多行 SQLite 表中查找重复项

Question

我想管理包含不同数量条目的列表。目前我正在使用以下表结构。

CREATE TABLE lists (
    list_id INTEGER,
    sort_id INTEGER,
    content_id INTEGER,
    PRIMARY KEY (list_id,sort_id)
)

所以我可以使用以下方式调用单个列表:

SELECT content_id WHERE list_id=<<X>> ORDER BY sort_id

原则上添加新列表不是问题，但是在插入一组新列表之前如何检查完整列表的重复项？我举个例子:

已插入以下列表:

list_id | sort_id | content_id
--------+---------+-----------
    3   |    1    |     7
    3   |    2    |     4
    3   |    3    |     9

内容为 7,4,9 的新列表将是重复的，不得插入。但由于我定义了不同的列表内容，所以必须插入内容 4,7,9 或 7,4 或 7,4,9,5。

我想添加一个新表，其中包含列表的串联内容，例如:

CREATE TABLE lists_concat (list_id PRIMARY KEY, content_concat UNIQUE)

因此，对于我的示例，将有条目

list_id | content_concat
--------+---------------
    3   |     7,4,9

这可行，但似乎很复杂。有更容易的方法吗？我也愿意为我的列表使用另一个表结构。请告诉我你的想法。

非常感谢

Answer 1

两个列表相等，如果

1. sort_id的数量相等，并且如果
2. 所有 sort_id 的值都匹配，即不存在具有匹配 sort_id 但值不同的行。

假设新列表存储在单独的表中:

CREATE TEMPORARY TABLE new_list ( sort_id, content_id );

您可以像这样搜索重复项:

SELECT list_id
FROM (SELECT DISTINCT list_id
      FROM lists) AS L1
WHERE (SELECT COUNT(*)
       FROM lists as L2
       WHERE L2.list_id = L1.list_id
      ) =
      (SELECT COUNT(*)
       FROM new_list
      )
  AND NOT EXISTS (SELECT 1
                  FROM lists AS L3
                  JOIN new_list ON L3.list_id = L1.list_id
                               AND L3.sort_id = new_list.sort_id
                  WHERE L3.content_id != new_list.content_id);