如何在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;

希望連接和基於序列的解決方案現在應該很明顯。