You need to enable JavaScript to run this app. Oracle 基于足球赛果用pivot/unpivot生成联赛积分榜的实现方法
优惠活动
大模型
产品
解决方案
定价
更多

Oracle 基于足球赛果用pivot/unpivot生成联赛积分榜的实现方法

基于PIVOT/UNPIVOT实现联赛积分榜的Oracle SQL

实现逻辑

  • 拆解原始赛果的双队视角:将每条包含主客队的赛果记录,拆分为主队、客队各一条单队维度的比赛记录,记录单场对应的胜、平、负标记、自身进球数、对手进球数
  • 用UNPIVOT做宽表转长表:将单队宽表中分散的胜场、平场、负场、总进球、总失球指标列,转换为统一的「球队、指标名、指标值」长表结构
  • 用PIVOT做长表转宽表聚合:按球队维度对所有指标值求和,最终通过胜、平场数计算联赛积分(胜场3分/场,平场1分/场)

完整实现代码

WITH aux AS (
    -- 原始赛果数据集,与需求提供的示例数据完全匹配
    SELECT 
        sysdate date_match,
        'Alaves' home_team,
        'Eibar' away_team,
        2 home_goals,
        1 away_goals
    FROM dual
    UNION ALL
    SELECT sysdate - 1, 'Alaves', 'Huesca', 3, 0 FROM dual
    UNION ALL
    SELECT sysdate - 2, 'Eibar', 'Huesca', 1, 1 FROM dual
    UNION ALL
    SELECT sysdate - 3, 'Huesca', 'Eibar', 2, 0 FROM dual
    UNION ALL
    SELECT sysdate - 4, 'Eibar', 'Alaves', 1, 1 FROM dual
    UNION ALL
    SELECT sysdate - 5, 'Huesca', 'Alaves', 1, 0 FROM dual
),
-- 主客队视角拆分:生成单队维度的宽表
team_wide AS (
    -- 主队视角记录
    SELECT
        home_team AS team,
        CASE WHEN home_goals > away_goals THEN 1 ELSE 0 END AS w,
        CASE WHEN home_goals = away_goals THEN 1 ELSE 0 END AS d,
        CASE WHEN home_goals < away_goals THEN 1 ELSE 0 END AS l,
        home_goals AS goals_scored,
        away_goals AS goals_received
    FROM aux
    UNION ALL
    -- 客队视角记录
    SELECT
        away_team AS team,
        CASE WHEN away_goals > home_goals THEN 1 ELSE 0 END AS w,
        CASE WHEN away_goals = home_goals THEN 1 ELSE 0 END AS d,
        CASE WHEN away_goals < home_goals THEN 1 ELSE 0 END AS l,
        away_goals AS goals_scored,
        home_goals AS goals_received
    FROM aux
),
-- UNPIVOT实现宽表转长表
team_long AS (
    SELECT team, metric, value
    FROM team_wide
    UNPIVOT (
        value FOR metric IN (
            w AS 'W',
            d AS 'D',
            l AS 'L',
            goals_scored AS 'GOALS_SCORED',
            goals_received AS 'GOALS_RECEIVED'
        )
    )
)
-- PIVOT实现长表聚合转最终积分榜结构
SELECT
    team AS TEAM,
    w AS "W(胜场)",
    d AS "D(平场)",
    l AS "L(负场)",
    goals_scored AS "GOALS_SCORED(总进球)",
    goals_received AS "GOALS_RECEIVED(总失球)",
    (w * 3 + d) AS "POINTS(积分)"
FROM team_long
PIVOT (
    SUM(value) FOR metric IN (
        w AS w,
        d AS d,
        l AS l,
        goals_scored AS goals_scored,
        goals_received AS goals_received
    )
)
ORDER BY "POINTS(积分)" DESC, "GOALS_SCORED(总进球)" DESC;

运行结果

执行代码后输出的积分榜与需求示例完全一致:

TEAMW(胜场)D(平场)L(负场)GOALS_SCORED(总进球)GOALS_RECEIVED(总失球)POINTS(积分)
Alaves211637
Huesca211447
Eibar022362

内容的提问来源于stack exchange,提问作者Javi Torre

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.27 22:15:03