MySQL 8.0如何按时间顺序配对a与后续b记录计算日期差

实现思路 #

你需要的配对逻辑本质是按时间升序排列的第n条a记录，匹配同样按时间升序排列的第n条b记录，用MySQL 8.0支持的窗口函数ROW_NUMBER()可以快速实现：

1. 分别对name为a、name为b的记录按DATE字段升序编号，生成独立的序号列
2. 按序号关联两类记录，直接计算日期差值即可

完整SQL代码 #

WITH a_rank AS (
    SELECT 
        id AS id_a,
        DATE AS date_a,
        ROW_NUMBER() OVER(ORDER BY DATE ASC) AS rn
    FROM test
    WHERE name = 'a'
),
b_rank AS (
    SELECT 
        id AS id_b,
        DATE AS date_b,
        ROW_NUMBER() OVER(ORDER BY DATE ASC) AS rn
    FROM test
    WHERE name = 'b'
)
SELECT 
    a_rank.id_a,
    b_rank.id_b,
    DATEDIFF(b_rank.date_b, a_rank.date_a) AS difference
FROM a_rank
INNER JOIN b_rank ON a_rank.rn = b_rank.rn;

输出验证 #

执行上述代码后输出结果和你期望的完全一致：

注意事项 #

如果a和b的记录数量不一致，INNER JOIN会自动舍弃数量更多的那一侧多出来的记录，如果你需要保留多出来的记录，可以改成LEFT JOIN或者RIGHT JOIN调整逻辑。

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