Oracle中为多日期行ID创建透视表 并筛选仅含2015年前日期的ID

问题背景 #

现有一张包含ID和Date列的表，同一ID对应多行数据，每行仅含一个日期值。原始数据如下：

ID   Date
1    01/01/2015
1    02/01/2015
1    03/01/2014
2    01/01/2014
3    02/01/2015
3    01/01/2014

期望生成如下格式的透视表（将同一ID的日期横向展开）：

ID   DATE         DATE        DATE
1    01/01/2015   02/01/2015  03/01/2014
2    01/01/2014   NULL        NULL
3    02/01/2015   01/01/2014  NULL

最终核心需求：找出所有关联日期都早于2015年的ID。直接使用WHERE Date < '2015-01-01'筛选无效，因为这类筛选会保留那些同时存在2015及以后日期的ID的旧日期行，不符合需求。

一、生成透视表 #

透视表的实现依赖具体SQL方言，以下是主流数据库的实现方式：

1. SQL Server（使用PIVOT语法） #

先为每个ID的日期生成序号，再通过PIVOT横向展开：

WITH RankedDates AS (
    SELECT 
        ID,
        Date,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date DESC) AS DateRank
    FROM YourTableName
)
SELECT 
    ID,
    [1] AS DATE_1,
    [2] AS DATE_2,
    [3] AS DATE_3
    -- 可根据实际最大日期行数增加更多列
FROM RankedDates
PIVOT (
    MAX(Date) FOR DateRank IN ([1], [2], [3])
) AS PivotTable;

2. MySQL（条件聚合实现） #

MySQL无原生PIVOT，用条件聚合模拟：

SELECT
    ID,
    MAX(CASE WHEN DateRank = 1 THEN Date END) AS DATE_1,
    MAX(CASE WHEN DateRank = 2 THEN Date END) AS DATE_2,
    MAX(CASE WHEN DateRank = 3 THEN Date END) AS DATE_3
FROM (
    SELECT
        ID,
        Date,
        @rank := IF(@prev_id = ID, @rank + 1, 1) AS DateRank,
        @prev_id := ID
    FROM YourTableName, (SELECT @rank := 0, @prev_id := NULL) AS vars
    ORDER BY ID, Date DESC
) AS RankedDates
GROUP BY ID;

3. PostgreSQL（条件聚合实现） #

SELECT
    ID,
    MAX(CASE WHEN DateRank = 1 THEN Date END) AS DATE_1,
    MAX(CASE WHEN DateRank = 2 THEN Date END) AS DATE_2,
    MAX(CASE WHEN DateRank = 3 THEN Date END) AS DATE_3
FROM (
    SELECT
        ID,
        Date,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date DESC) AS DateRank
    FROM YourTableName
) AS RankedDates
GROUP BY ID;

二、筛选仅含2015年前日期的ID #

以下两种方法均可准确实现需求：

方法1：使用NOT EXISTS #

通过子查询检查当前ID是否存在2015及以后的日期，仅保留无此类日期的ID：

SELECT DISTINCT ID
FROM YourTableName t1
WHERE NOT EXISTS (
    SELECT 1
    FROM YourTableName t2
    WHERE t2.ID = t1.ID
    AND t2.Date >= '2015-01-01'
);

方法2：使用GROUP BY + HAVING #

通过分组后检查ID的最大日期是否早于2015年：

SELECT ID
FROM YourTableName
GROUP BY ID
HAVING MAX(Date) < '2015-01-01';

这两种方法均会返回仅包含2015年之前日期的ID（示例中为ID=2）。

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