获取所有连续行之间的时间差(最新一个不打印)
问题描述:
我试图从下表中检索所有列数据以及所有连续行之间的时间差,其中(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
我怎样才能正确地获得所有连续记录之间的时间差,同时还打印所有的人?
答
我会用一个相关子查询:
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)
的持续时间(而不是下一次)。我认为第一个版本更容易测试,看看发生了什么。
+0
您的查询完美无缺,我只需在where子句中添加括号以防止“next_send_date”始终是第一个记录日期。谢谢 ! –
编辑您的问题并提供预期结果。 –