Oracle数据库中tb_lose与tb_profit表关联查询问题求助

原SQL的核心问题是未按Name聚合，导致结果中同一Name对应多条记录（匹配不同Level），且大量字段为空。以下两种方案可实现按Name一行展示Level 5和10的盈亏数据：

方案一：条件聚合（CASE WHEN + GROUP BY） #

兼容性强，逻辑直观：

SELECT
    COALESCE(l.Name, p.Name) AS Name,
    MAX(CASE WHEN l.Level = 5 THEN l.Lose END) AS Lose_5,
    MAX(CASE WHEN p.Level = 5 THEN p.Profit END) AS Profit_5,
    MAX(CASE WHEN l.Level = 10 THEN l.Lose END) AS Lose_10,
    MAX(CASE WHEN p.Level = 10 THEN p.Profit END) AS Profit_10
FROM
    TB_Lose l
FULL OUTER JOIN TB_Profit p
    ON l.Name = p.Name AND l.Level = p.Level
GROUP BY
    COALESCE(l.Name, p.Name)

• FULL OUTER JOIN确保不遗漏仅在单张表存在的Name记录；
• CASE WHEN筛选对应Level的字段值，MAX聚合取唯一值（若同一Name+Level有多条记录，可替换为SUM等符合业务逻辑的聚合函数）；
• COALESCE处理单表无对应Name的场景。

方案二：Oracle PIVOT语法（11g及以上版本支持） #

语法更简洁，适合Oracle高版本环境：

WITH combined_data AS (
    SELECT
        COALESCE(l.Name, p.Name) AS Name,
        l.Level AS lose_level,
        l.Lose,
        p.Level AS profit_level,
        p.Profit
    FROM TB_Lose l
    FULL OUTER JOIN TB_Profit p
        ON l.Name = p.Name AND l.Level = p.Level
)
SELECT
    Name,
    Lose_5,
    Profit_5,
    Lose_10,
    Profit_10
FROM combined_data
PIVOT (
    MAX(Lose) AS Lose, MAX(Profit) AS Profit
    FOR (lose_level) IN (5 AS "_5", 10 AS "_10")
)

• 先通过CTE合并两张表的基础数据；
• PIVOT直接将Level维度的行数据转为列，生成对应Level的盈亏字段。

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