mysql - 获取所有连续行之间的时间差(最新行不打印)

Question

我正在尝试检索所有列数据以及下表中所有连续行之间的时间差，其中 (sender_id = 1 OR = 2) 和 (recipient_id = 2 OR = 1)。

CREATE TABLE records (
    id INT(11) AUTO_INCREMENT,
    send_date DATETIME NOT NULL,
    content TEXT NOT NULL,
    sender_id INT(11) NOT NULL,
    recipient_id INT(11) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO records (send_date, content, sender_id, recipient_id) VALUES
('2013-08-23 14:50:00', 'record 1/5', 1, 2),
('2013-08-23 14:51:00', 'record 2/5', 2, 1),
('2013-08-23 15:50:00', 'record 3/5', 2, 1),
('2013-08-23 15:50:13', 'record 4/5', 1, 2),
('2013-08-23 16:50:00', 'record 5/5', 1, 2);

问题是我的选择查询由于 WHERE 子句而不会输出最新记录:

SELECT t1.content, DATE_FORMAT(t1.send_date, '%b, %D, %H:%i') AS 'pprint_date', 
       TIMESTAMPDIFF(MINUTE, t1.send_date, t2.send_date) AS 'duration' 
FROM records t1, records t2 
WHERE (t1.id = t2.id - 1) /*<= this subtraction excludes latest record*/
  AND ((t1.sender_id = 1 AND t1.recipient_id = 2) 
   OR (t1.sender_id = 2 AND t1.recipient_id = 1))
ORDER BY t1.id ASC

如何在打印所有连续记录的同时正确获取所有连续记录之间的时间差？

Answer 1

我会使用相关子查询:

select r.*,
       (select r2.send_date
        from records r2
        where (r2.sender_id in (1, 2) or r2.recipient_id in (1, 2)) and
              r2.send_date > r.send_date
        order by r2.send_date asc
        limit 1
       ) as next_send_date
from records r
where r.sender_id in (1, 2) or r.recipient_id in (1, 2);

您可以通过在子查询中使用TIMESTAMPDIFF(MINUTE, r.send_date, r2.send_date)来获取持续时间(而不是下一次)。我认为第一个版本更容易让您测试以了解发生了什么。