我刚刚想到:
假设您有一个唯一对 (a, b) 的表:
CREATE table t1 (a INT, b INT, PRIMARY KEY (a, b));
现在你用以下内容填充它:
INSERT INTO t1
VALUES (1,1), (1,2), -- item to compare with
(2,1), (2,3), -- has one common prop with 1
(3,1), (3,2), -- has the same props as 1
(4,1), (4,2), (4,3), (4,4); -- has 2 same props with 1
以下查询将根据相似性对其他项目进行排序:
SELECT t1.a,
COUNT(t2.a) as same_props_count,
ABS(COUNT(t2.a) - COUNT(*)) as diff_count
FROM t1
LEFT JOIN t1 as t2 ON t1.b = t2.b and t2.a = 1
WHERE t1.a <> 1
GROUP BY t1.a
ORDER BY same_props_count DESC, diff_count;
a, same_props_count, diff_count
3, 2, 0
4, 2, 2
2, 1, 1