Mysql LEFT JOIN ON 倒数第二个 id 仅

Question

我有这个查询试图计算时间总和:

SELECT t1.id, t1.number, t1.time - COALESCE(SUM(t2.time), 0) time FROM times t1 
LEFT JOIN times t2 ON t1.number = t2.number AND t2.id < t1.id
GROUP BY t1.id, t1.number, t1.time

输出:

id          number          time (in seconds)
--------------------------------------------
1            9              65
2            5              78
3            7              92

它工作正常，但我只需要与子句中 LEFT JOIN 条件的倒数第二个 id 求和 ( t2.id < t1.id )。

Answer 1

用于选择倒数第二个 id

SELECT t1.id, t1.number, t1.time - COALESCE(SUM(t2.time), 0) time FROM times t1 
LEFT JOIN times t2 ON t1.number = t2.number 
AND t2.id = (select id from times order by id desc limit 1,1)
GROUP BY t1.id, t1.number, t1.time