mysql - 查找一行和多行之间的相似性

Question

免责声明:我对 MySQL 很陌生，这样做是为了了解更多信息的实验。

本质上，我有一个表，其中一行是“主”行。我想将这一行与其他几行进行比较。我想从这一行中选择最“正确”或与主行最相似的行并将其存储到变量中。如果没有几行与主行足够相似，我只想取消该操作。

我可以想出一种通过迭代来做到这一点的方法，但我一直读到迭代对于 SQL 来说是非常低效/糟糕的做法，并且想看看是否还有其他方法。

在此示例中，我希望选择提交 2，并拒绝提交 1 和 3。

Type, Answer 1, Answer 2, Answer 3
Master, 100, 200, 300
Submission 1, 100, 400, 300
Submission 2, 100, 200, 300
Submission 3, 200, 100, 300

但是，在下面的示例中，我想选择提交 1，尽管两者并不完全相同。

Type, Answer 1, Answer 2, Answer 3
Master, 100, 200, 300
Submission 1, 100, 400, 300
Submission 2 , 100, 500, 500
Submission 3, 200, 100, 300

最后，在这里我想选择提交 1，因为它出现在提交 3 之前，尽管两者的正确程度相同。

Type, Answer 1, Answer 2, Answer 3
Master, 100, 200, 300
Submission 1, 100, 400, 300
Submission 2 , 100, 500, 500
Submission 3, 500, 200, 300

Answer 1

尝试以下查询。它计算 Master 行与其他行共有的答案数。在正确答案数量平局的情况下，它使用每个错误答案的数值差异来选择最接近的匹配。

SELECT t.Type, t.Answer1, t.Answer2, t.Answer3,
       (t.a1 + t.a2 + t.a3) AS numRight,
       (t.d1 + t.d2 + t.d3) AS numOff
FROM
(
    SELECT t2.Type, t2.Answer1, t2.Answer2, t2.Answer3,
           CASE WHEN t1.Answer1 = t2.Answer1 THEN 1 ELSE 0 END AS a1,
           CASE WHEN t1.Answer2 = t2.Answer2 THEN 1 ELSE 0 END AS a2,
           CASE WHEN t1.Answer3 = t2.Answer3 THEN 1 ELSE 0 END AS a3,
           ABS(t1.Answer1 - t2.Answer1) ASd1,
           ABS(t1.Answer2 - t2.Answer2) AS d2,
           ABS(t1.Answer3 - t2.Answer3) AS d3
    FROM yourTable t1 INNER JOIN yourTable t2
        ON t1.Type = 'Master' AND t2.Type <> 'Master'
) t
ORDER BY numRight DESC, numOff

如果您只需要一行，可以将 LIMIT 1 添加到查询末尾。

此处演示:

SQLFiddle