MySQL查询：按用户分组取Top5热门选中项并拼接为字符串

要实现你需求的查询，我们可以借助窗口函数来给每个用户的物品按出现次数排名，再筛选出Top5后用GROUP_CONCAT拼接成字符串。以下是详细实现步骤：

假设表结构 #

先假设你的表名为user_item_selections（如果实际表名不同，替换即可），核心字段为：

• user_id：用户ID
• item_id：用户选择的物品ID（对应你说的chosen_user_items）

完整查询语句 #

SELECT 
    user_id,
    GROUP_CONCAT(item_id ORDER BY occurrence_count DESC, item_id ASC SEPARATOR ',') AS top_5_items
FROM (
    -- 第一步：统计每个用户每个物品的出现次数，并按用户分组排名
    SELECT 
        user_id,
        item_id,
        COUNT(*) AS occurrence_count,
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY COUNT(*) DESC, item_id ASC
        ) AS rank_num
    FROM user_item_selections
    GROUP BY user_id, item_id
) ranked_items
-- 第二步：筛选每个用户的前5名物品
WHERE rank_num <= 5
GROUP BY user_id;

关键逻辑说明 #

1. 内层子查询：

   • 先按user_id和item_id分组，统计每个物品被用户选择的次数occurrence_count
   • 使用ROW_NUMBER()窗口函数，按用户分组（PARTITION BY user_id），然后先按出现次数降序、再按物品ID升序排序，给每个物品分配排名rank_num
   • 这里用ROW_NUMBER()而不是RANK()，是为了确保当出现次数相同时，按物品ID排序后能稳定取到前5个（如果用RANK()，可能出现多个并列第5的情况，导致结果超过5个）

2. 外层查询：

   • 筛选出排名前5的物品（rank_num <=5）
   • 按user_id分组，用GROUP_CONCAT拼接物品ID，并且指定拼接时的排序规则和分隔符，确保结果和你示例的顺序一致

示例验证 #

对应你给出的例子（user_id=1的情况）：

• item_id=1出现5次（rank 1），item_id=3出现3次（rank2），item_id=2出现2次（rank3），item_id=4、5各出现1次（rank4、5），item_id=6出现1次（rank6被过滤）
• 最终拼接结果就是1,3,2,4,5，和你预期完全一致

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