选择两个人作为captain和vice captain, 其他人为player
WITH captain_vice AS (
SELECT t1.player_id AS captain_id, t2.player_id AS vice_id
FROM tbl_match_players t1
JOIN tbl_match_players t2
ON t1.player_id <> t2.player_id
)
SELECT cv.*, GROUP_CONCAT(p.player_id) AS players
FROM captain_vice cv
JOIN tbl_match_players p
ON p.player_id NOT IN (cv.captain_id, vice_id)
GROUP BY cv.captain_id, cv.vice_id;