MySQL查询中强制包含指定记录并随机选取其余记录的需求

原查询 #

原本用于随机选取3名击球手（player_type=2）ID的查询：

SELECT 
    GROUP_CONCAT(player_id) AS batsman
FROM
    (SELECT 
        player_id
    FROM
        tbl_match_players
    WHERE
        player_type = 2 AND player_match_id = 1
    ORDER BY player_id NOT IN (1) , RAND()
    LIMIT 3) t
ORDER BY RAND()

需求变更 #

新增is_compulsory字段后，要求：

• 所有is_compulsory=1的符合条件记录必须包含在结果中
• 结果总条数不超过3条
• 剩余名额从is_compulsory=0的符合条件记录中随机选取

解决方案 #

使用如下查询实现需求：

SELECT GROUP_CONCAT(player_id ORDER BY RAND()) AS batsman
FROM (
    -- 提取所有强制入选的击球手
    SELECT player_id
    FROM tbl_match_players
    WHERE player_type = 2 AND player_match_id = 1 AND is_compulsory = 1
    
    UNION ALL
    
    -- 随机选取剩余名额的非强制击球手
    SELECT player_id
    FROM tbl_match_players
    WHERE player_type = 2 AND player_match_id = 1 AND is_compulsory = 0
    ORDER BY RAND()
    LIMIT GREATEST(0, 3 - (
        SELECT COUNT(*) 
        FROM tbl_match_players 
        WHERE player_type = 2 AND player_match_id = 1 AND is_compulsory = 1
    ))
) combined_records;

逻辑说明 #

1. 强制记录优先：第一个子查询获取所有必须包含的is_compulsory=1记录；
2. 动态计算剩余名额：用GREATEST(0, 3 - 强制记录数)避免出现负数LIMIT，确保当强制记录数≥3时，不会选取任何非强制记录；
3. 随机补充：第二个子查询对非强制记录随机排序后取剩余名额的数量；
4. 随机拼接结果：外层的GROUP_CONCAT带ORDER BY RAND()确保最终输出的ID顺序是随机的。

测试结果 #

结合给定的测试数据，player_type=2的强制记录为ID 2、3，剩余1个名额会从4、5中随机选取，最终结果为2,3,4或2,3,5（顺序随机），完全符合需求。

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