mysql - 在 MySQL SUM 时间戳差异中实现 IF

Question

我有以下查询，求和时差，我希望它将负时差设置为零或忽略它。

SELECT 
       SUM(TIMESTAMPDIFF(MINUTE, GREATEST('2017-10-16 16:00:00',created_at),LEAST('2017-10-17 00:00:00',IFNULL(closed,NOW()))

                         )) AS totalWork, created_at, closed
         FROM cavity_actions WHERE  cavity_id = 52 AND action_id = 10 AND job_id = 42 GROUP BY created_at, closed 

此查询返回:

totalWork    created_at            closed
-12588       2017-10-07 21:12:48   2017-10-07 22:11:08
480          2017-10-07 22:17:45   NULL

我需要忽略负值或将其设置为零。

我尝试在 WHERE 子句中使用 totalWork 别名，但生成了未知列错误。

是否有任何方法允许在变量中设置每个 TIMESTAMPDIFF() 输出，然后在 IF 子句中使用它？

我试过了:

SELECT 
        SUM(@td := TIMESTAMPDIFF(MINUTE, GREATEST('2017-10-16 16:00:00',created_at),LEAST('2017-10-17 00:00:00',IFNULL(closed,NOW()))

                         ) IF(td > 0,td,0)) AS totalWork, created_at, closed
         FROM cavity_actions WHERE cavity_id = 52 AND action_id = 10 AND job_id = 42 GROUP BY created_at, closed

但它返回错误。

Answer 1

不需要IF()(或CASE，这是 ANSI 标准)。您可以使用GREATEST():

SELECT SUM(GREATEST(TIMESTAMPDIFF(MINUTE,
                                  GREATEST('2017-10-16 16:00:00', created_at),
                                  LEAST('2017-10-17 00:00:00', COALESCE(closed, NOW()))

                                 ), 0
                   )
          ) AS totalWork,
       created_at, closed
FROM cavity_actions
WHERE cavity_id = 52 AND action_id = 10 AND job_id = 42
GROUP BY created_at, closed