如何用SQL按共同艺术家数量对其他用户排序或取Top N？

我来给你详细说明怎么写查询语句，先假设你的关联表名叫user_artist（字段是user_id和artist_id，用来记录用户和艺术家的关联关系），user表的主键是user_id。

基础版：按共同艺术家数量排序所有其他用户 #

下面的SQL会针对特定用户（比如user_id=1的user1），把其他用户按共同拥有的艺术家数量从多到少排序：

SELECT 
    other_user.user_id,
    COUNT(DISTINCT ua.artist_id) AS common_artist_count
FROM 
    user_artist target_ua
JOIN 
    user_artist ua ON target_ua.artist_id = ua.artist_id
JOIN 
    user other_user ON ua.user_id = other_user.user_id
WHERE 
    target_ua.user_id = 1  -- 替换成你要查询的目标用户ID
    AND other_user.user_id != 1  -- 排除目标用户自己
GROUP BY 
    other_user.user_id
ORDER BY 
    common_artist_count DESC, other_user.user_id ASC;

逻辑拆解：

1. 先通过target_ua获取目标用户（user1）的所有关联艺术家；
2. 关联user_artist表，找到所有喜欢这些艺术家的用户；
3. 关联user表拿到其他用户的信息（如果只需要user_id，其实可以省略这步，直接用ua.user_id）；
4. 按用户分组，统计每个用户和目标用户的共同艺术家数量（用DISTINCT避免同一艺术家被重复统计，比如如果关联表有重复记录的话）；
5. 最后按共同数量降序排序，数量相同的话按用户ID升序排列。

进阶版：筛选出共同艺术家最多的N个用户 #

如果只需要前N个（比如前2个），只需要在查询末尾加上LIMIT N就行：

SELECT 
    other_user.user_id,
    COUNT(DISTINCT ua.artist_id) AS common_artist_count
FROM 
    user_artist target_ua
JOIN 
    user_artist ua ON target_ua.artist_id = ua.artist_id
JOIN 
    user other_user ON ua.user_id = other_user.user_id
WHERE 
    target_ua.user_id = 1
    AND other_user.user_id != 1
GROUP BY 
    other_user.user_id
ORDER BY 
    common_artist_count DESC, other_user.user_id ASC
LIMIT 2;  -- 替换成你需要的N值

拿你举的例子来说，执行这个查询后，结果会是user2（count=2）排在前面，user3（count=1）紧随其后，完全符合你的预期~

小提示 #

如果你的user_artist表在user_id和artist_id上建立了联合索引，这个查询的性能会大幅提升，尤其是数据量比较大的时候。

内容的提问来源于stack exchange，提问作者Lewiky