Wie sortiere ich doppelte Zeilen in einer Schlüssel-Wert-Paar-Tabelle in mehreren Spalten in MySQL?

Question

Ich habe also die folgende Tabelle mit Schlüssel/Wert-Paaren, der Benutzer übermittelt Daten über das Formular und jede Frage im Formular wird hier als separate Zeile zur Tabelle hinzugefügt. Submission_id identifiziert jede Formularübermittlung.

+----+---------------+----------- ---+--------+
|. id |. Einreichungs-ID |
+----+---------------+--------------+--------+
|. 10 |
|. 2 |. 10 |
|. 3 |. 10 |
|. 4 |. 15 |
|. 5 |. 15 |
|. 6 |. 15 |
|. 7 |. 20 |
|. 8 |. 20 |
|. 9 |. 20 |
+----+---------------+--------------+--------+

;

Wie aus den obigen Daten ersichtlich ist, haben die Commits mit den IDs 10 und 15 den gleichen Wert (nur die Commit-IDs sind unterschiedlich). Dies liegt im Wesentlichen daran, dass der Benutzer dasselbe Formular zweimal eingereicht hat, es sich also um ein Duplikat handelt.

Ich versuche eine Möglichkeit zu finden, diese Tabellen so zu sortieren, dass alle doppelten Einreichungen in der richtigen Reihenfolge angezeigt werden. Anhand der obigen Tabelle versuche ich, eine Abfrage zu erstellen, die folgende Ergebnisse liefert:

+---------------+
|. Einreichungs-ID |
+-------------+
|. 10 |
|. 15 |
|. 20 |
+---------------+

Ich möchte also prüfen, ob ein Commit den gleichen Wert für die Schlüssel manufacturer, model hat. Wenn ja, erhalten sie die Commit-ID und werden in den Ergebnissen nebeneinander platziert. Es gibt andere Schlüssel in der tatsächlichen Tabelle, aber ich möchte nur Duplikate basierend auf diesen drei Schlüsseln (Hersteller, Modell, Vorname) abgleichen.

Ich habe lange darüber nachgedacht und versucht, mögliche Lösungen zu finden, habe aber nichts Verlässliches gefunden.

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;

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