问题:如何在不排除其他时间跨度的相关数据的情况下,接受下面的查询并让它忽略结果中可能为负数的任何时间跨度?(换句话说,我不能把条件放在WHERE子句中,因为时间跨度1和2可能是好的,但时间跨度3是负的,但我想保留它们。
我是否在AVG函数中放入案例陈述?有没有更有效的方法来运行这个?
SELECT DATE(tbl.time1) AS 'date',
AVG((time_to_sec(timediff(tbl.time2 , tbl.time1))/3600) ) AS 'avg_time1',
AVG((time_to_sec(timediff(tbl.time3 , tbl.time2))/3600) ) AS 'avg_time2',
AVG((time_to_sec(timediff(tbl.time4 , tbl.time3))/3600) ) AS 'avg_time3',
FROM tbl
WHERE DATE(tbl.time1) = '$variable'
GROUP BY DATE(tbl.time1)
我发现它使用了重复亚历克斯提到的原理在这里:
SELECT AVG(`a`), AVG(IF(`b` > -1, `b`, NULL)), AVG(`c`) FROM `t`;
我相信您希望使用HAVING
关键字?
SELECT DATE(tbl.time1) AS 'date',
AVG((time_to_sec(timediff(tbl.time2 , tbl.time1))/3600) ) AS 'avg_time1',
AVG((time_to_sec(timediff(tbl.time3 , tbl.time2))/3600) ) AS 'avg_time2',
AVG((time_to_sec(timediff(tbl.time4 , tbl.time3))/3600) ) AS 'avg_time3',
FROM tbl
WHERE
AND DATE(tbl.time1) = '$variable'
GROUP BY DATE(tbl.time1)
HAVING avg_time1 > 0 AND avg_time2 > 0 and avg_time3 > 0;
您可以使用case
语句:
SELECT DATE(tbl.time1) AS "date",
(CASE WHEN AVG(time_to_sec(timediff(tbl.time2 , tbl.time1))/3600) >= 0
THEN AVG(time_to_sec(timediff(tbl.time2 , tbl.time1))/3600)
END) as avg_time1,
(CASE WHEN AVG(time_to_sec(timediff(tbl.time3 , tbl.time2))/3600) >= 0
THEN AVG(time_to_sec(timediff(tbl.time3 , tbl.time2))/3600)
END) as avg_time2,
(CASE WHEN AVG(time_to_sec(timediff(tbl.time4 , tbl.time3))/3600) >= 0
THEN AVG(time_to_sec(timediff(tbl.time4 , tbl.time3))/3600)
END) as avg_time3
FROM tbl
WHERE DATE(tbl.time1) = '$variable'
GROUP BY DATE(tbl.time1);
这将返回NULL
值,而不是负值,我猜这正是您想要的。
此外,您应该只对日期和字符串文字值使用单引号,而不对标识符(如列名)使用单引号。尽管MySQL允许这种语法,但使用它通常会导致混乱。