Oracle 19c无查询实现行列转换并显示全部测试类型

核心优化思路 #

• 避免多子查询重复扫描表：用窗口函数一次性筛选出每个用户各测试类型的最新记录，仅扫描原表一次
• 确保全量测试类型覆盖：先构造「所有用户 + 4种测试类型」的基础数据集，再关联最新测试结果，保证无数据的类型显示'U'
• 用PIVOT高效转列：替代手动条件聚合，语法更简洁且Oracle对PIVOT有原生优化

具体实现SQL #

假设原表名为test_results，字段包括TestID, UserID, TestType, TestDate, Result：

WITH user_test_types AS (
    -- 构造所有用户与4种测试类型的全量组合
    SELECT DISTINCT u.UserID, t.TestType
    FROM test_results u
    CROSS JOIN (
        SELECT 'A01' AS TestType FROM DUAL
        UNION ALL SELECT 'A02' FROM DUAL
        UNION ALL SELECT 'B08' FROM DUAL
        UNION ALL SELECT 'B17' FROM DUAL
    ) t
),
latest_test_results AS (
    -- 筛选每个用户各测试类型的最新记录
    SELECT UserID, TestType, Result
    FROM (
        SELECT 
            UserID, 
            TestType, 
            Result,
            ROW_NUMBER() OVER (PARTITION BY UserID, TestType ORDER BY TestDate DESC) AS rn
        FROM test_results
        WHERE TestType IN ('A01', 'A02', 'B08', 'B17') -- 提前过滤无关测试类型，减少计算量
    )
    WHERE rn = 1
)
-- 关联全量组合与最新结果，转列并替换空值为'U'
SELECT 
    ut.UserID,
    NVL(p.A01, 'U') AS A01,
    NVL(p.A02, 'U') AS A02,
    NVL(p.B08, 'U') AS B08,
    NVL(p.B17, 'U') AS B17
FROM user_test_types ut
LEFT JOIN latest_test_results ltr
    ON ut.UserID = ltr.UserID AND ut.TestType = ltr.TestType
PIVOT (
    MAX(Result) FOR TestType IN ('A01' AS A01, 'A02' AS A02, 'B08' AS B08, 'B17' AS B17)
) p
ORDER BY ut.UserID;

优化点说明 #

1. 窗口函数筛选最新记录：ROW_NUMBER()按UserID+TestType分组，仅一次扫描原表即可获取所有用户的最新测试结果，彻底避免4个子查询重复扫描表的性能开销
2. 全量组合构造：通过CROSS JOIN生成用户与测试类型的所有可能组合，确保即使某用户没有某类型的测试记录，也能在结果中显示该类型并填充'U'
3. 提前过滤数据：在窗口函数子查询中加入WHERE TestType IN (...)，只处理目标测试类型，减少不必要的数据计算量
4. PIVOT转列：Oracle 19c对PIVOT有优化，比手动写CASE WHEN的条件聚合更高效且可读性更强

备选方案（条件聚合） #

如果更习惯用条件聚合写法，性能与PIVOT相当：

WITH latest_test_results AS (
    SELECT UserID, TestType, Result
    FROM (
        SELECT 
            UserID, 
            TestType, 
            Result,
            ROW_NUMBER() OVER (PARTITION BY UserID, TestType ORDER BY TestDate DESC) AS rn
        FROM test_results
        WHERE TestType IN ('A01', 'A02', 'B08', 'B17')
    )
    WHERE rn = 1
),
user_test_types AS (
    SELECT DISTINCT UserID FROM test_results
)
SELECT 
    ut.UserID,
    NVL(MAX(CASE WHEN TestType = 'A01' THEN Result END), 'U') AS A01,
    NVL(MAX(CASE WHEN TestType = 'A02' THEN Result END), 'U') AS A02,
    NVL(MAX(CASE WHEN TestType = 'B08' THEN Result END), 'U') AS B08,
    NVL(MAX(CASE WHEN TestType = 'B17' THEN Result END), 'U') AS B17
FROM user_test_types ut
LEFT JOIN latest_test_results ltr
    ON ut.UserID = ltr.UserID
GROUP BY ut.UserID
ORDER BY ut.UserID;

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