更新时间:2023-02-17 17:19:14
SELECT u2.user_id
-- join our user to their scores
FROM (users u1 JOIN scores s1 USING (user_id))
-- and then join other users and their scores
JOIN (users u2 JOIN scores s2 USING (user_id))
ON s1.item_id = s2.item_id
AND u1.user_id != u2.user_id
-- filter for our user of interest
WHERE u1.user_id = ?
-- group other users' scores together
GROUP BY u2.user_id
-- and here's the magic: order in descending order of "distance" between
-- our selected user and all of the others: you may wish to weight
-- self_ranking differently to item scores, in which case just multiply
-- appropriately
ORDER BY SUM(ABS(s2.score - s1.score))
+ ABS(u2.self_ranking - u1.self_ranking) DESC