如何在MySQL中按指定品牌颜色组合随机查询且每组最多返回3条？

表结构 #

+------------+--------------+
| Field      | Type         |
+------------+--------------+
| id         | varchar(20)  |
| make       | varchar(255) |
| color      | varchar(20)  |
+------------+--------------+

输入数据 #

+---------+---------+---------+
|   ID    | Make    |  Color  |
+---------+---------+---------+
|    0    | Toyota  |   Red   |
|    1    | Toyota  |   Red   |
|    2    | Toyota  |   Blue  |
|    3    | Honda   |   Red   |
|    4    | Mazda   |   Blue  |
|    5    | Mazda   |   Red   |
|    6    | Toyota  |   Red   |
|    7    | Honda   |   Blue  |
|    8    | Nissan  |   Blue  |
|    9    | Nissan  |   Blue  |
|    10   | Nissan  |   Red   |
|    11   | Nissan  |   Red   |
|    12   | Nissan  |   Blue  |
|    13   | Nissan  |   Blue  |
|    14   | Nissan  |   Blue  |
|    15   | Nissan  |   Red   |
|    16   | Nissan  |   Blue  |
+---------+---------+---------+

查询需求 #

筛选品牌为Toyota、Honda或Mazda，颜色为Blue或Red的数据，并且每个（品牌+颜色）组合随机最多返回3条记录。例如Toyota+Red有4条数据，仅随机取3条；Honda和Mazda的符合条件组合各2条，全部返回。不关心结果顺序，需平衡代码可读性与查询复杂度。

方法1：窗口函数实现（MySQL 8.0+ 推荐） #

这是最简洁高效的单查询方案，利用ROW_NUMBER()窗口函数按组合分组随机排序后取前3条：

SELECT id, make, color
FROM (
    SELECT 
        id, 
        make, 
        color,
        ROW_NUMBER() OVER (
            PARTITION BY make, color 
            ORDER BY RAND()
        ) AS row_num
    FROM your_table_name
    WHERE 
        make IN ('Toyota', 'Honda', 'Mazda')
        AND color IN ('Blue', 'Red')
) AS ranked
WHERE row_num <= 3;

• 逻辑：子查询按make+color分组，每组内用RAND()随机排序，ROW_NUMBER()给每组记录编序号，外层筛选序号≤3的结果即可。
• 优势：单查询完成，性能优于多次查询，代码逻辑清晰易读。

方法2：兼容低版本MySQL（无窗口函数） #

如果MySQL版本低于8.0，可用变量模拟分组排序：

SELECT id, make, color
FROM (
    SELECT 
        id, 
        make, 
        color,
        @row_num := CASE 
            WHEN @prev_make = make AND @prev_color = color THEN @row_num + 1 
            ELSE 1 
        END AS row_num,
        @prev_make := make,
        @prev_color := color
    FROM your_table_name,
         (SELECT @row_num := 0, @prev_make := '', @prev_color := '') AS vars
    WHERE 
        make IN ('Toyota', 'Honda', 'Mazda')
        AND color IN ('Blue', 'Red')
    ORDER BY make, color, RAND()
) AS ranked
WHERE row_num <= 3;

• 注意：依赖ORDER BY make, color, RAND()确保同组记录连续，变量才能正确计数，逻辑稍复杂但兼容旧版本。

方法3：多次查询拼接（可读性优先） #

如果组合数量少（如本次的6个组合），多次查询后用UNION ALL拼接的代码更直观，性能差异可忽略：

-- Toyota+Red
SELECT id, make, color FROM your_table_name WHERE make='Toyota' AND color='Red' ORDER BY RAND() LIMIT 3
UNION ALL
-- Toyota+Blue
SELECT id, make, color FROM your_table_name WHERE make='Toyota' AND color='Blue' ORDER BY RAND() LIMIT 3
UNION ALL
-- Honda+Red
SELECT id, make, color FROM your_table_name WHERE make='Honda' AND color='Red' ORDER BY RAND() LIMIT 3
UNION ALL
-- Honda+Blue
SELECT id, make, color FROM your_table_name WHERE make='Honda' AND color='Blue' ORDER BY RAND() LIMIT 3
UNION ALL
-- Mazda+Red
SELECT id, make, color FROM your_table_name WHERE make='Mazda' AND color='Red' ORDER BY RAND() LIMIT 3
UNION ALL
-- Mazda+Blue
SELECT id, make, color FROM your_table_name WHERE make='Mazda' AND color='Blue' ORDER BY RAND() LIMIT 3;

• 用UNION ALL而非UNION避免不必要的去重操作，每个子查询单独控制随机取数，代码一目了然。

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