Mysql时差不工作Mysql客户端版本:3.23.49

Question

TIMEDIFF函数不工作我想得到列类型=1到列类型=1和old_value=10之间的时间差我的2个问题如下。

SELECT date_modified
FROM mantis_bug_history_table
WHERE bug_id =75616
AND TYPE =0
AND old_value =10
ORDER BY date_modified DESC 
output
------
2019-10-28 10:26:57
SELECT date_modified
FROM mantis_bug_history_table
WHERE bug_id =75616
AND TYPE =1
ORDER BY date_modified DESC
output
------
2019-10-28 10:15:04

[在此输入图像描述][1]错误SQL查询：文档

SELECT TIMEDIFF(
SELECT date_modified
FROM mantis_bug_history_table
WHERE bug_id =75616
AND TYPE =1
ORDER BY date_modified DESC ,
SELECT date_modified
FROM mantis_bug_history_table
WHERE bug_id =75616
AND TYPE =0
AND old_value =10
ORDER BY date_modified DESC )
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT date_modified
FROM mantis_bug_history_table
WHERE bu

上面的查询试图获得时差

Answer 1

在子查询周围添加括号(...subquery)，然后作为参数传递给TIMEDIFF:

(SELECT date_modified FROM mantis_bug_history_table WHERE bug_id =75616 AND TYPE =0 AND old_value =10 ORDER BY date_modified DESC )
(SELECT date_modified FROM mantis_bug_history_table WHERE bug_id =75616 AND TYPE =1 ORDER BY date_modified DESC)

最终查询：

SELECT TIMEDIFF((SELECT date_modified FROM mantis_bug_history_table WHERE bug_id =75616 AND TYPE =0 AND old_value =10 ORDER BY date_modified DESC ),
(SELECT date_modified FROM mantis_bug_history_table WHERE bug_id =75616 AND TYPE =1 ORDER BY date_modified DESC));

请注意：TIMEDIFF函数期望每个子查询只返回一行+列，如果将来可能有多个，则考虑添加LIMIT1。