mysql - 在同一个 Mysql 查询中的 MySql GROUP BY 和 SUM()

Question

我想对总秒数求和，比如答案是 total_seconds 49058，billable 是 540，它按 billable 分组，我已经尝试了下面提到的 mysql 查询。

ID   billable total_seconds
----------------------------
9326      480          2254
9352       60          3657
9352       60          2756
9352       60            14
9326      480          2607
9326      480           122
9326      480           162
9326      480           559
9326      480          2478
9326      480           313
9326      480           234
9326      480           548
9326      480          2400
9326      480           564
9326      480          4449
9326      480         12986
9326      480         12942
9326      480            13

查询:

SELECT DISTINCT 
    sub.user_id,
    e.name, e.employee_id,
    sub.id, sub.billable,
    history.total_seconds
FROM 
    v_wa_tasks as t
JOIN 
    v_wa_sub_tasks as sub ON t.id = sub.task_id 
JOIN 
    v_wa_task_histories as history ON sub.id = history.sub_task_id
JOIN 
    v_employees as e ON sub.user_id = e.id  
WHERE 
    e.id = 18 
    AND t.status_id = 5 
    AND t.status_id != 9 
    AND t.task_nature_id != 4  
    AND sub.review_type = 0 
    AND t.start_time BETWEEN "2017-10-05 00:00:00" AND "2017-10-05 23:59:00"

Answer 1

select distinct和 sum很少一起工作。几乎总是您需要做更多的子查询工作，不仅要获得正确的总和，还要减少行数。这是总结历史的一种可能方法:

SELECT
      sub.user_id
    , e.NAME
    , e.employee_id
    , sub.id
    , sub.billable
    , history.total_seconds
FROM v_wa_tasks AS t
INNER JOIN v_wa_sub_tasks AS sub ON t.id = sub.task_id
INNER JOIN (
      SELECT
            sub_task_id
          , SUM(total_seconds) total_seconds
      FROM v_wa_task_histories
      GROUP BY
            sub_task_id
) AS history ON sub.id = history.sub_task_id
INNER JOIN v_employees AS e ON sub.user_id = e.id
WHERE e.id = 18
AND t.status_id = 5
AND t.status_id != 9
AND t.task_nature_id != 4
AND sub.review_type = 0
AND t.start_time >= '2017-10-05' AND t.start_time < '2017-10-06'

注意:“23:59:59”不是一天的结束，使用它可能有一天会给您带来麻烦(MySQL 现在支持子-秒精度)。最好的解决方案是避免在日期范围内使用“介于”之间。使用 >=和 <相反，但请注意，您将较高的日期“推高”到第二天。参见 Bad habits to kick : mis-handling date / range queries