如何在MySQL中对键值对表中的重复行进行多列排序？

Question

所以我有以下的键/值对表格，用户通过表单提交数据，表单上的每个问题都作为一个单独的行添加到这里的表格中。 Submission_id 标识每个表单提交。

+----+---------------+--------------+--------+
| id | submission_id |     key      | value  |
+----+---------------+--------------+--------+
|  1 |            10 | manufacturer | Apple  |
|  2 |            10 | model        | 5s     |
|  3 |            10 | firstname    | Paul   |
|  4 |            15 | manufacturer | Apple  |
|  5 |            15 | model        | 5s     |
|  6 |            15 | firstname    | Paul   |
|  7 |            20 | manufacturer | Apple  |
|  8 |            20 | model        | 5s     |
|  9 |            20 | firstname    | Andrew |
+----+---------------+--------------+--------+

从上面的数据可以看出，id为10和15的提交具有相同的值（只是提交id不同）。这基本上是因为用户提交了相同的表单两次，所以是重复的。

我试图找到一种方法来对这些表格进行排序，使得任何重复的提交按顺序出现在一起。给定上面的表格，我试图构建一个查询，给我以下结果：

+---------------+
| submission_id |
+---------------+
|            10 |
|            15 |
|            20 |
+---------------+

所以我想检查一下，如果一个提交的 manufacturer、model 和 firstname 键具有相同的值。如果是这样，那么它们将获得提交id，并将它们放在结果中相邻的位置。在实际的表格中还有其他键，但我只想根据这3个键（manufacturer、model、firstname）来匹配重复项。

我已经反复思考了很长时间，并尝试寻找一些可能的解决方案，但没有找到可靠的方法。

Answer 1

这不是一个键值表。通常被称为实体-属性-值表/关系/模式。

看问题，如果表按照常规的第一和第二范式排列，这将是微不足道的 - 只需对值进行连接，按照这些值进行分组，并进行计数....

SELECT manufacturer, model, firstname, COUNT(DISTINCT submission_id)
FROM atable
GROUP BY  manufacturer, model, firstname
HAVING COUNT(DISTINCT submission_id)>1;

或者使用连接....

SELECT a.manufacturer, a.model, a.firstname
, a.submission_id, b.submission_id
FROM atable a
JOIN atable b
ON a.manufacturer=b.manufacturer
AND a.model=b.model
AND a.firstname=b.firstname
WHERE a.submission_id<b.submission_id
;

或者使用排序和比较相邻行....

SELECT *
FROM
(
SELECT @prev.submission_id AS prev_submission_id
, @prev.manufacturer AS prev_manufacturer
, @prev.model AS prev_model
, @prev.firstname AS pref_firstname
, a.submission_id
, a.manufacturer
, a.model
, set @prev.submission_id:=a.submission_id as currsid
, set @prev.manufacturer:=a.manufacturer as currman
, set @prev.model:=a.model as currmodel
, set @prev.firstname=a.forstname as currname
FROM atable
ORDER BY manufacturer, model, firstname, submission_id
)
WHERE prev_manufacturer=manufacturer
AND prev_model=model
AND prev_firstname=firstname
AND prev_submission_id<>submission_id;

所以解决方案就是简单地使您的数据看起来像一个正常的关系....

SELECT ilv.values
, COUNT(ilv.submission_id)
, GROUP_CONCAT(ilv.submission_id)
FROM 
 (SELECT a.submission_id
  , GROUP_CONCAT(CONCAT(a.key, '=',a.value)) AS values
  FROM atable a
  GROUP BY a.submission_id
 ) ilv
GROUP BY ilv.values
HAVING COUNT(ilv.submission_id)>1;

希望连接和基于序列的解决方案现在应该很明显。