Oracle数据库如何在查询结果中生成不存在于源表的多列数据

解决方案 #

方案1：通用SQL写法（兼容所有数据库，优先推荐） #

不需要用到PIVOT语法，直接通过分组聚合就能生成目标列，代码如下：

SELECT 
    -- 此处替换为你需要分组的维度列，例如task_id、project_id等
    task_id,
    MIN(run_date) AS earlist_run_date,
    MAX(run_date) AS last_rundate,
    COUNT(CASE WHEN run_date > CURRENT_DATE THEN 1 END) AS remainng_run_dates
FROM T1
-- 分组列和SELECT里的非聚合列保持一致
GROUP BY task_id;

这个写法逻辑简洁，性能也更优，适合绝大多数场景。

方案2：PIVOT语法实现 #

如果你必须用PIVOT语法实现，需要先在子查询中构造出三个指标的名称和对应值，再做行转列，示例代码（以Oracle/SQL Server为例）：

SELECT * FROM (
    SELECT 
        task_id,
        indicator,
        val
    FROM (
        SELECT 
            task_id,
            -- 构造三个统计指标的对应值
            MIN(run_date) OVER (PARTITION BY task_id) AS earlist_run_date,
            MAX(run_date) OVER (PARTITION BY task_id) AS last_rundate,
            COUNT(CASE WHEN run_date > CURRENT_DATE THEN 1 END) OVER (PARTITION BY task_id) AS remainng_run_dates
        FROM T1
    ) t
    -- 行转列前把指标拆为多行
    UNPIVOT (
        val FOR indicator IN (earlist_run_date, last_rundate, remainng_run_dates)
    ) up
) t
PIVOT (
    MAX(val)
    FOR indicator IN ('EARLIST_RUN_DATE' AS earlist_run_date, 'LAST_RUNDATE' AS last_rundate, 'REMAINNG_RUN_DATES' AS remainng_run_dates)
) p;

如果你的数据库不支持UNPIVOT，也可以用UNION ALL手动构造指标行：

SELECT * FROM (
    SELECT DISTINCT task_id, 'earlist_run_date' AS indicator, MIN(run_date) OVER (PARTITION BY task_id) AS val FROM T1
    UNION ALL
    SELECT DISTINCT task_id, 'last_rundate' AS indicator, MAX(run_date) OVER (PARTITION BY task_id) AS val FROM T1
    UNION ALL
    SELECT DISTINCT task_id, 'remainng_run_dates' AS indicator, COUNT(CASE WHEN run_date > CURRENT_DATE THEN 1 END) OVER (PARTITION BY task_id) AS val FROM T1
) t
PIVOT (
    MAX(val)
    FOR indicator IN ('earlist_run_date', 'last_rundate', 'remainng_run_dates')
) p;

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