SQL实现：为数据表每行匹配时间最近的指定分组日期

首先先明确你的表结构和初始数据（我把日期格式调整为标准ISO格式，避免不同数据库的解析兼容性问题）：

CREATE TABLE t1 (dates DATE, groups NUMBER);
INSERT INTO t1 VALUES('2020-03-01', 1);
INSERT INTO t1 VALUES('2020-03-02', 2);
INSERT INTO t1 VALUES('2020-03-10', 3);
INSERT INTO t1 VALUES('2020-04-01', 10);
INSERT INTO t1 VALUES('2020-04-02', 20);
INSERT INTO t1 VALUES('2020-04-10', 3);

你的需求是为每一行找到当前日期之后最近的groups=3记录的dates值，对于本身groups=3的行，可以返回NULL或者下一个groups=3的日期。下面提供两种通用的实现方案：

方案一：使用LATERAL JOIN（推荐，适配PostgreSQL、Oracle、SQL Server、MySQL 8.0+） #

这种方式通过横向关联，为每一行单独筛选后续符合条件的第一条记录，逻辑清晰且性能表现不错：

SELECT
    t.dates,
    t.groups,
    next_3d.dates AS DATE_OF_NEXT_3D_GROUP
FROM t1 t
LEFT JOIN LATERAL (
    SELECT dates
    FROM t1
    WHERE dates > t.dates
      AND groups = 3
    ORDER BY dates ASC
    LIMIT 1
) next_3d ON TRUE
ORDER BY t.dates;

说明： #

• LATERAL JOIN允许子查询引用主查询的t.dates字段，实现逐行匹配后续数据
• 子查询里用dates > t.dates限定只找当前行之后的记录，ORDER BY dates ASC LIMIT 1精准拿到最近的那一条groups=3的日期
• 对于没有后续groups=3的行（比如最后一行2020-04-10），该字段会返回NULL；如果想让groups=3的行自动返回下一个3的日期，这个逻辑也刚好满足

方案二：使用CTE+子查询（适配更多数据库，包括老版本MySQL） #

如果你的数据库不支持LATERAL JOIN，可以先用公共表表达式提取所有groups=3的日期，再通过子查询匹配最近的后续日期：

WITH group3_dates AS (
    SELECT dates AS group3_date
    FROM t1
    WHERE groups = 3
)
SELECT
    t.dates,
    t.groups,
    (
        SELECT MIN(group3_date)
        FROM group3_dates
        WHERE group3_date > t.dates
    ) AS DATE_OF_NEXT_3D_GROUP
FROM t1 t
ORDER BY t.dates;

说明： #

• CTEgroup3_dates先把所有groups=3的日期提取出来，简化后续查询逻辑
• 子查询用MIN(group3_date)找到大于当前行日期的最小日期，也就是最近的后续3分组日期
• 同样，没有后续3分组的行返回NULL，groups=3的行如果有下一个3分组会返回对应日期，否则返回NULL

自定义调整：让groups=3的行固定返回NULL #

如果需要让groups=3的行的DATE_OF_NEXT_3D_GROUP强制为NULL，只需要在方案一中加个CASE判断即可：

SELECT
    t.dates,
    t.groups,
    CASE WHEN t.groups != 3 THEN next_3d.dates ELSE NULL END AS DATE_OF_NEXT_3D_GROUP
FROM t1 t
LEFT JOIN LATERAL (
    SELECT dates
    FROM t1
    WHERE dates > t.dates
      AND groups = 3
    ORDER BY dates ASC
    LIMIT 1
) next_3d ON TRUE
ORDER BY t.dates;

最终验证结果 #

执行任意方案后，都会得到符合你需求的结果：

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