Oracle SQL动态生成多位置球员所有组合球队的实现问询

需求核心说明 #

该需求本质是多维度笛卡尔积按维度展开：每个位置作为一个独立集合，取各集合1个元素组成所有可能的组合，再将每个组合拆分为每行对应一个位置的球员数据，全程无需硬编码位置相关值，适配任意运动的位置数量、球员数量。

实现SQL #

WITH pos_order AS (
    -- 动态获取所有位置并分配唯一序号
    SELECT position, ROW_NUMBER() OVER(ORDER BY position) pos_seq
    FROM (SELECT DISTINCT position FROM player_tbl)
),
player_rn AS (
    -- 给每个位置下的球员分配独立序号
    SELECT 
        p.player_id, p.position, p.player,
        po.pos_seq,
        ROW_NUMBER() OVER(PARTITION BY p.position ORDER BY p.player_id) player_rn
    FROM player_tbl p
    INNER JOIN pos_order po ON p.position = po.position
),
max_pos AS (
    -- 获取位置总数，作为递归终止条件
    SELECT MAX(pos_seq) max_seq FROM pos_order
),
recur_teams AS (
    -- 递归生成所有组合的球员序号串
    SELECT 
        pos_seq,
        TO_CHAR(player_rn) combo_rn_str,
        player_id,
        player,
        position
    FROM player_rn
    WHERE pos_seq = 1
    UNION ALL
    SELECT 
        pr.pos_seq,
        rt.combo_rn_str || ',' || pr.player_rn,
        pr.player_id,
        pr.player,
        pr.position
    FROM recur_teams rt
    INNER JOIN player_rn pr ON pr.pos_seq = rt.pos_seq + 1
),
all_teams AS (
    -- 过滤完整组合并分配唯一球队ID
    SELECT 
        ROW_NUMBER() OVER(ORDER BY combo_rn_str) team_id,
        combo_rn_str
    FROM recur_teams
    CROSS JOIN max_pos mp
    WHERE pos_seq = mp.max_seq
)
-- 最终展开为要求的输出格式
SELECT 
    t.team_id,
    pr.position,
    pr.player
FROM all_teams t
INNER JOIN player_rn pr 
    ON REGEXP_SUBSTR(t.combo_rn_str, '\d+', 1, pr.pos_seq) = TO_CHAR(pr.player_rn)
ORDER BY t.team_id, pr.pos_seq;

方案说明 #

1. 完全动态适配：无需修改SQL即可适配任意运动的位置数量、球员数量，没有任何硬编码的位置名称或数值
2. 结果准确性：针对样例数据可直接生成120个球队、600行结果，与预期计算逻辑完全一致
3. 兼容性：支持Oracle 11gR2及以上版本（递归CTE和正则函数均为该版本后原生支持），如果位置数量超过100，可在recur_teams的第一个SELECT前加/*+ recursive_cte_depth(所需最大深度) */提示调整递归上限
4. 性能说明：如果各位置球员总数较多，笛卡尔积规模会快速膨胀，属于需求本身的逻辑特性，可根据实际业务场景添加过滤条件缩小范围

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