如何限制GROUP_CONCAT分组拼接时每组最多返回前6条结果

核心思路是先按所有者分组给内部物品排序，仅保留每个所有者的前6个物品后再做聚合，不会触发全局截断。

方案1：支持窗口函数的数据库环境（MySQL 8.0+、PostgreSQL、SQLite 3.25+ 等主流版本均可使用） #

改写后的SQL如下：

WITH all_items AS (
    SELECT 
        owner.id AS owner_id,
        item.title,
        item.importance,
        -- 按所有者分区、内部按重要性排序分配唯一行号
        ROW_NUMBER() OVER (PARTITION BY owner.id ORDER BY item.importance DESC) AS rn
    FROM owner
    JOIN item ON item.owner_id = owner.id
)
SELECT 
    owner_id,
    GROUP_CONCAT(DISTINCT title ORDER BY importance SEPARATOR ',') AS item_list
FROM all_items
WHERE rn <= 6 -- 仅保留每个所有者的前6个物品
GROUP BY owner_id

说明：

• PARTITION BY owner.id保证行号是每个所有者单独计算，不会出现全局截断的问题，符合不能在公用表表达式中使用全局LIMIT的要求
• 原查询中外层重复JOIN owner表的逻辑可以直接省略，公用表表达式中已经包含了所有者ID信息，简化后查询效率更高
• 如果需要保留重要性并列第6的所有物品，将ROW_NUMBER()替换为RANK()即可。

方案2：兼容不支持窗口函数的低版本MySQL环境 #

可以用关联子查询实现相同逻辑：

SELECT 
    o.id AS owner_id,
    GROUP_CONCAT(DISTINCT i.title ORDER BY i.importance SEPARATOR ',') AS item_list
FROM owner o
JOIN item i ON i.owner_id = o.id
WHERE (
    SELECT COUNT(*) 
    FROM item i2 
    WHERE i2.owner_id = o.id 
    AND i2.importance >= i.importance
) <= 6
GROUP BY o.id

说明：关联子查询会统计每个所有者下比当前物品重要性更高的物品数量，仅保留数量小于等于6的物品，再进行聚合拼接。

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