高级sql查询

选择两个人作为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;