基于SQL实现交替主客场的单循环赛制赛程生成

针对你提出的单循环赛制需求（主客场交替、奇数队轮空），以下是无需数组迭代的纯SQL实现方案，兼容多数主流数据库（PostgreSQL/MySQL等）。

前提假设 #

假设存在teams表存储队伍信息：

CREATE TABLE teams (
    team_id INT PRIMARY KEY,
    team_name VARCHAR(50) NOT NULL
);

-- 示例数据（可替换为你的实际队伍）
INSERT INTO teams VALUES
(1, 'Team A'), (2, 'Team B'), (3, 'Team C'), (4, 'Team D'), (5, 'Team E');

核心SQL代码 #

WITH team_list AS (
    -- 为队伍添加行号，用于轮次和对阵计算
    SELECT 
        team_id, 
        team_name,
        ROW_NUMBER() OVER (ORDER BY team_id) AS rn
    FROM teams
),
params AS (
    -- 计算总队伍数、总轮次：偶数队轮次为n-1，奇数队轮次为n（含轮空）
    SELECT 
        COUNT(*) AS total_teams,
        CASE WHEN COUNT(*) % 2 = 0 THEN COUNT(*) - 1 ELSE COUNT(*) END AS total_rounds
    FROM teams
),
rounds AS (
    -- 生成轮次序列（PostgreSQL用GENERATE_SERIES，MySQL见下方适配）
    SELECT GENERATE_SERIES(1, (SELECT total_rounds FROM params)) AS round_num
),
team_round_matches AS (
    -- 计算每队每轮的对手，处理轮空逻辑
    SELECT
        r.round_num,
        t1.team_id AS home_team_id,
        t1.team_name AS home_team,
        -- 计算对手ID
        CASE
            WHEN (SELECT total_teams FROM params) % 2 = 0 THEN
                t2.team_id
            ELSE
                CASE WHEN t1.rn = r.round_num THEN NULL ELSE t2.team_id END
        END AS away_team_id,
        -- 计算对手名称（轮空显示-byte-）
        CASE
            WHEN (SELECT total_teams FROM params) % 2 = 0 THEN
                t2.team_name
            ELSE
                CASE WHEN t1.rn = r.round_num THEN '-bye-' ELSE t2.team_name END
        END AS away_team,
        -- 标记是否轮空
        CASE WHEN (SELECT total_teams FROM params) % 2 = 1 AND t1.rn = r.round_num THEN 1 ELSE 0 END AS is_bye
    FROM team_list t1
    CROSS JOIN rounds r
    LEFT JOIN team_list t2 ON 
        CASE
            WHEN (SELECT total_teams FROM params) % 2 = 0 THEN
                -- 偶数队对阵规则：固定首队，其余队伍轮换
                t2.rn = CASE 
                    WHEN t1.rn = 1 THEN (r.round_num % (SELECT total_teams - 1 FROM params)) + 1
                    ELSE (t1.rn + r.round_num - 2) % (SELECT total_teams - 1 FROM params) + 2
                END
            ELSE
                -- 奇数队对阵规则：轮空队伍对应当前轮次行号，其余队伍轮换
                t2.rn = CASE
                    WHEN t1.rn = 1 THEN (r.round_num % (SELECT total_teams FROM params)) + 1
                    ELSE (t1.rn + r.round_num - 2) % (SELECT total_teams FROM params) + 1
                END
                AND t2.rn != r.round_num
        END
),
unique_matches AS (
    -- 过滤重复对阵（避免A vs B和B vs A同时出现）
    SELECT
        round_num,
        home_team_id,
        home_team,
        away_team_id,
        away_team,
        is_bye
    FROM team_round_matches
    WHERE 
        is_bye = 1
        OR home_team_id < away_team_id
),
final_schedule AS (
    -- 调整主客场，实现逐轮交替
    SELECT
        round_num,
        -- 通过队伍行号+轮次的奇偶性决定主客场，确保交替
        CASE WHEN (t.rn + round_num) % 2 = 0 THEN home_team ELSE away_team END AS Home,
        CASE WHEN (t.rn + round_num) % 2 = 0 THEN away_team ELSE home_team END AS Away
    FROM unique_matches um
    JOIN team_list t ON t.team_id = um.home_team_id
    WHERE um.is_bye = 0
    UNION ALL
    -- 加入轮空场次
    SELECT
        round_num,
        home_team AS Home,
        away_team AS Away
    FROM unique_matches
    WHERE is_bye = 1
)
-- 最终输出
SELECT
    round_num AS "轮次",
    Home,
    Away
FROM final_schedule
ORDER BY round_num, Home;

关键逻辑说明 #

1. 轮次生成：用序列生成工具（或递归CTE）生成所有轮次，无需迭代
2. 对阵计算：通过行号与轮次的模运算实现单循环轮换，确保每队仅与其他队交手一次
3. 主客场交替：利用队伍行号+轮次的奇偶性组合，动态调整主客场，保证同一队伍的主客场逐轮交替
4. 奇数队轮空：每轮让行号等于轮次的队伍轮空，确保每队仅轮空一次，轮空不计入主客场统计
5. 去重处理：通过home_team_id < away_team_id过滤重复对阵，避免双向重复

MySQL适配说明 #

MySQL不支持GENERATE_SERIES，可替换为递归CTE生成轮次：

WITH RECURSIVE rounds AS (
    SELECT 1 AS round_num
    UNION ALL
    SELECT round_num + 1 FROM rounds 
    WHERE round_num < (SELECT CASE WHEN COUNT(*) %2=0 THEN COUNT(*)-1 ELSE COUNT(*) END FROM teams)
)

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