MySQL查询需求：优先包含指定player_id、强制选手后随机补全

数据库表结构 #

CREATE TABLE `tbl_match_players` (
  `player_id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `player_match_id` int NOT NULL,
  `player_team` varchar(255) NOT NULL,
  `player_name` varchar(255) NOT NULL,
  `player_type` tinyint NOT NULL DEFAULT 1 COMMENT '1 = 守门员 2 = 击球手 3 = 全能选手 4 = 投球手',
  `is_compulsory` mediumint NOT NULL DEFAULT 0,
  `player_points` mediumint NOT NULL DEFAULT 0
);

INSERT INTO `tbl_match_players` VALUES
( 1, 2, '印度'     , '选手1' , 1, 0, 0),
( 2, 2, '印度'     , '选手2' , 2, 0, 0),
( 3, 2, '印度'     , '选手3' , 2, 1, 0),
( 4, 2, '荷兰'     , '选手4' , 2, 1, 0),
( 5, 2, '印度'     , '选手5' , 2, 0, 0),
( 6, 2, '荷兰'     , '选手6' , 2, 0, 0),
( 7, 2, '印度'     , '选手7' , 2, 0, 0),
( 8, 2, '印度'     , '选手8' , 2, 0, 0),
( 9, 2, '印度'     , '选手9' , 4, 0, 0),
(10, 2, '荷兰'     , '选手10', 4, 0, 0),
(11, 2, '印度'     , '选手11', 4, 0, 0);

当前查询语句 #

当前查询会优先包含is_compulsory=1的选手ID，再随机选取剩余符合条件的选手ID至限定数量：

SELECT GROUP_CONCAT(player_id) AS batsman
FROM (
    SELECT player_id
    FROM tbl_match_players
    WHERE player_type = 2 AND player_match_id = 2
    ORDER BY is_compulsory DESC, RAND()
    LIMIT 5
)t ORDER BY RAND();

修改需求 #

需要调整查询，支持传入逗号分隔的选手ID列表，实现以下逻辑：

• 若指定的选手ID满足player_type=2且player_match_id=2的条件，必须包含这些ID
• 若is_compulsory=1的选手ID满足上述WHERE条件，必须包含这些ID
• 剩余名额从满足WHERE条件的其他选手ID中随机选取，补全至限定数量（5个）

示例：当指定选手ID为5,6时，查询结果应类似5,6,3,4,2或5,6,3,4,7。

修改后的查询语句 #

SET @selected_players = '5,6'; -- 替换为需要指定的逗号分隔选手ID
SET @limit = 5;

SELECT GROUP_CONCAT(player_id ORDER BY RAND()) AS batsman
FROM (
    -- 1. 优先选取指定的符合条件的选手
    SELECT player_id
    FROM tbl_match_players
    WHERE player_type = 2 AND player_match_id = 2
      AND FIND_IN_SET(player_id, @selected_players) > 0
    
    UNION ALL
    
    -- 2. 加入符合条件且不在指定列表中的强制选手
    SELECT player_id
    FROM tbl_match_players
    WHERE player_type = 2 AND player_match_id = 2
      AND is_compulsory = 1
      AND FIND_IN_SET(player_id, @selected_players) = 0
    
    UNION ALL
    
    -- 3. 随机选取剩余名额的符合条件选手（排除已选的）
    SELECT player_id
    FROM tbl_match_players
    WHERE player_type = 2 AND player_match_id = 2
      AND FIND_IN_SET(player_id, @selected_players) = 0
      AND is_compulsory = 0
    ORDER BY RAND()
    LIMIT GREATEST(0, @limit - (
        SELECT COUNT(*)
        FROM tbl_match_players
        WHERE player_type = 2 AND player_match_id = 2
          AND (FIND_IN_SET(player_id, @selected_players) > 0 OR is_compulsory = 1)
    ))
) AS combined;

逻辑说明 #

1. 指定选手优先：先筛选出符合WHERE条件的指定选手ID，确保必选
2. 强制选手补充：加入符合条件且未被指定的is_compulsory=1选手
3. 随机补全名额：计算剩余需要的名额数，从其他符合条件的选手中随机选取补全
4. 最终结果通过GROUP_CONCAT随机排序返回，保证输出顺序随机

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