对if语句使用group函数无效

ilmyapht  于 2021-06-25  发布在  Mysql
关注(0)|答案(1)|浏览(323)

使用此sql时出现“组函数使用无效”错误

SELECT 
  SUM(overTime) AS overTimeTotal 
  FROM (
    SELECT
    SUM(IF(
      SUM(TIMESTAMPDIFF(SECOND, starttid, sluttid)) >= TIME_TO_SEC('07:24:00:0000'),
      SUM(TIMESTAMPDIFF(SECOND, starttid, sluttid) - TIME_TO_SEC('07:24:00:0000')),
      0)) AS overTime
    FROM hours
    WHERE user_id = :id
    AND month(starttid) = :selectedMonth) AS T

表结构和数据:

INSERT INTO `hours` (`id`, `user_id`, `starttid`, `sluttid`) VALUES
(89, 1, '2018-05-09 05:00:00', '2018-05-09 16:00:00'),
(90, 1, '2018-05-08 05:00:00', '2018-05-08 10:00:00');

所以我尝试用这个sql来获取总加班时间,但是当我的加班时间为负时,就像在id90(2:24:0000)中一样——它从总加班时间中减去,这不是我想要的。如果加班时间是负数,我想把总和设为零,这样我只得到实际的加班时间。在这种情况下,我期望的加班结果是:3:36:0000,但我得到1:12:0000。所以我一直在试图绕过这个问题,现在我遇到了标题中描述的错误。
我该怎么做,我想要什么?

lx0bsm1f

lx0bsm1f1#

看起来你聚合得太快了。
似乎要计算每行的超时时间,然后进行聚合。

SELECT SUM( 
          IF( TIMESTAMPDIFF(SECOND, h.starttid, h.sluttid) >= TIME_TO_SEC('07:24:00:0000')
            , TIMESTAMPDIFF(SECOND, h.starttid, h.sluttid)  - TIME_TO_SEC('07:24:00:0000')
            , 0
          )  
        ) AS overtimetotal
   FROM hours h
  WHERE h.user_id = :id
    AND MONTH(h.startid) = :selectedMonth

对于测试,删除aggregate sum()函数,并返回其他列

SELECT IF( TIMESTAMPDIFF(SECOND, h.starttid, h.sluttid) >= TIME_TO_SEC('07:24:00:0000')
            , TIMESTAMPDIFF(SECOND, h.starttid, h.sluttid)  - TIME_TO_SEC('07:24:00:0000')
            , 0
        )  AS overtime

      , GREATEST(0,
          TIMESTAMPDIFF(SECOND, h.starttid, h.sluttid)  - TIME_TO_SEC('07:24:00:0000')
        ) AS alt_overtime

      , h.id
      , h.starttid
      , h.sluttid
   FROM hours h
  WHERE h.user_id = :id
    AND ...

我还建议在where子句的 predicate 中使用裸列,而不是在列上运行函数。在文本端使用表达式返回与列数据类型匹配的datetime数据类型,例如。

AND h.startid  >= dt_month_begin + INTERVAL 0 MONTH
    AND h.startid  <  dt_month_begin + INTERVAL 1 MONTH

相关问题