如何修改MySQL查询实现每类最多2条、总计最多10条随机数据

可以利用MySQL的窗口函数ROW_NUMBER()实现按类别限制条数的需求，同时保证随机性并自动适配新增类别，具体SQL如下：

SELECT ID, catID
FROM (
    SELECT 
        d.ID, 
        c.catID,
        -- 按catID分组，每组内随机排序并生成序号
        ROW_NUMBER() OVER (PARTITION BY c.catID ORDER BY RAND()) AS rn
    FROM decks AS d
    LEFT JOIN deck_extra_cats AS c ON d.ID = c.deckID
    WHERE d.status = 'made'
) AS temp
-- 筛选每组最多2条数据
WHERE rn <= 2
-- 控制总返回条数不超过10条
LIMIT 10;

逻辑说明 #

• 内层子查询：通过PARTITION BY c.catID将数据按类别分组，ORDER BY RAND()让每组内的记录随机排序，ROW_NUMBER()为每组内的记录生成从1开始的序号。
• 外层查询：筛选序号≤2的记录，确保每个类别最多返回2条数据。
• 最终通过LIMIT 10限制总返回条数。

兼容低版本MySQL（无窗口函数） #

如果你的MySQL版本低于8.0，不支持窗口函数，可以用变量模拟分组序号：

SELECT ID, catID
FROM (
    SELECT 
        d.ID, 
        c.catID,
        @rn := IF(@current_cat = c.catID, @rn + 1, 1) AS rn,
        @current_cat := c.catID
    FROM decks AS d
    LEFT JOIN deck_extra_cats AS c ON d.ID = c.deckID
    CROSS JOIN (SELECT @current_cat := '', @rn := 0) AS vars
    WHERE d.status = 'made'
    ORDER BY c.catID, RAND()
) AS temp
WHERE rn <= 2
-- 最后随机打乱结果，保证整体随机性
ORDER BY RAND()
LIMIT 10;

注意事项 #

• 两种方案都无需手动指定类别，新增类别会自动被纳入分组逻辑。
• 内层的ORDER BY RAND()保证每组内的记录是随机选取的，若需要整体结果也随机，可在外层查询末尾追加ORDER BY RAND()。

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