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个键(制造商、型号、名字)来匹配重复的键。

我已经重复了很长一段时间，并试图寻找一些可能的解决方案，但无法得到可靠的东西。

Answer 1

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

看看这个问题，如果表是以常规的1+2格式排列的话，那就很简单了--你只需对这些值做一个连接，按这些值分组，然后进行计数.

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;

希望基于连接和序列的解决方案现在应该是显而易见的。