mysql - 根据共享的外国 ID 选择相同的项目

Question

数据库包含产品的集合；每个收集的产品在添加到集合时都会记录一个价格，以及一些其他值。

// `collections_products`

id collection_id group product_id option_id price
1  1             0     56         0         3.1920
2  1             0     56         54        1.2000
3  1             0     56         55        2.4000
4  1             0     56         56        3.6000
5  1             0     56         57        4.8000
6  1             0     56         58        6.0000
7  1             0     57         0         3.1920
8  1             0     57         54        1.2000

11  10           0     56         0         3.1920
12  10           0     56         54        1.2000
13  10           0     56         55        2.4000
14  10           0     56         56        3.6000
15  10           0     56         57        4.8000
16  10           0     56         58        6.0000
17  10           0     57         0         3.1920
18  10           0     57         54        1.2000

21  100          0     56         0         9.9999
22  100          0     56         54        9.9999
23  100          0     56         55        9.9999
24  100          0     56         56        9.9999
25  100          0     56         57        9.9999
26  100          0     56         58        9.9999
27  100          0     57         0         9.9999
28  100          0     57         54        9.9999


31  1000         0     56         0         3.1920
32  1000         0     56         54        1.2000
33  1000         0     56         55        2.4000
34  1000         0     56         56        3.6000

36  1000         0     56         58        6.0000
37  1000         0     57         0         3.1920
38  1000         0     57         54        1.2000

有一些collection_id ，我需要找到与给定集合相同的其他集合(具有相同的内容，即相同的产品、组和选项，价格相同；顺序不重要)。

在上面的例子中:

• 具有 collection_id 的行集10(B 组)是具有 collection_id 的行集的重复项1(A组)；对于 A 中的每一行，B 中都有另一行具有相同的 group product_id option_id price ，并且 A 和 B 的行数相同
• 具有 collection_id 的行集100 不是任何其他的重复，因为所有价格都不同
• 具有 collection_id 的行集1000 不是任何其他的重复，因为行数不同(与 collection_id 1 相比，缺少行 id 35)

想出:

• 有一个选择查询，根据公共(public) ID 和值查找其他集合，所有这些都在一个 SQL 语句中进行，但不确定这对于 MySQL 是否可行
• 计算每个集合结果集的校验和(组、product_id、option_id、每行的价格)，将其存储为 collections.checksum ，每次集合内发生移动时重新计算。搜索时，获取我拥有的集合的校验和，然后根据该校验和进行选择。

研究了校验和的想法。发现:

• MySQL rows checksum & mySQL: get hash value for each row? :对各个行进行校验，但不对结果集进行校验
• Checksum of SELECT results in MySQL :使用 CRC32 并有一个预期冲突警告，看起来很合理

不想重新发明轮子。很惊讶我找不到任何可重用的东西，除非我找错了方向。

解决这个问题的正确方法是什么？请指教

更新我不想删除任何集合，即使它们是重复的。我需要将它们结合起来。这是一个半虚构的例子，如果它没有 100% 有意义，抱歉

Answer 1

这样的事情应该有效:

SELECT `product_id`, `option_id`, `group`, `price`, COUNT(*) as count_occurrences 
FROM `collections_products`
GROUP BY `product_id`, `option_id`, `group`, `price`
HAVING count_occurrences > 1;

这将为您提供在数据集中多次出现的所有(product_id、option_id、price)组合。如果您还需要相关行的 ID，您可以使用 JOIN 执行子查询，如下所示:

SELECT cp.`id` FROM
(SELECT `product_id`, `option_id`, `group`, `price`, COUNT(*) as count_occurrences 
FROM `collections_products`
GROUP BY `product_id`, `option_id`, `group`, `price`
HAVING count_occurrences > 1) t1
LEFT JOIN `collections_products` cp
ON t1.`product_id` = cp.`product_id` 
AND t1.`option_id` = cp.`option_id` 
AND t1.`group` = cp.`group`
AND t1.`price` = cp.`price`;

更新:

要获取包含与给定集合相同产品的集合 ID，您需要如下所示的内容:

SELECT DISTINCT t2.`collection_id` FROM
(SELECT `collection_id`,`product_id`, `option_id`, `group`, `price`
FROM `collections_products`
WHERE `collection_id`=?) t1
LEFT JOIN `collections_products` t2
ON t1.`product_id`=t2.`product_id`
AND t1.`option_id`=t2.`option_id`
AND t1.`group`=t2.`group`
AND t1.`price`=t2.`price`
AND t1.`collection_id`<>t2.`collection_id`;