时间戳

首页 > 解决方案 > MYSQL如何根据日期和响应的多个条件求和

mysql - MYSQL如何根据日期和响应的多个条件求和

问题描述

我正在尝试根据 3 列汇总提供的小时数以及接受或拒绝的小时数。1 列是日期 2 列是小时 3 是标准。我需要将他们接受或拒绝 OT 的日期范围之间的小时数相加。我需要能够在指定的日期范围内计算响应是和否。@ 第 4 个总和行它不能正常工作，并且每隔一个总和行显示错误的计数似乎工作。看来我有两个以上的条件是失败的。

SELECT
  employee.employee_ID,
  employee.LastName,
  employee.FirstName,
  employee.Department,
  employee.HomePhone,
  employee.CellPhone,
  SUM(CASE WHEN ot_tracking.shiftDate BETWEEN '2020-12-01' AND '2020-12-31' AND ot_tracking.operatorResponse = 'Yes' THEN ot_tracking.hours else 0 END) AS OT_Accepted,
  SUM(CASE WHEN ot_tracking.shiftDate BETWEEN '2020-12-01' AND '2020-12-31' AND ot_tracking.operatorResponse = 'No' THEN ot_tracking.hours else 0 END) AS OT_Declind,
  SUM(CASE WHEN ot_tracking.shiftDate BETWEEN '2020-12-01' AND '2020-12-31' AND ot_tracking.operatorResponse = 'Not Available - working or off' THEN ot_tracking.hours else 0 END) AS 'OT Unable to work',
  SUM(CASE WHEN ot_tracking.shiftDate BETWEEN '202-12-01' AND '2020-12-31' AND ot_tracking.operatorResponse = 'Yes' OR ot_tracking.operatorResponse = 'No' THEN ot_tracking.hours else 0 END) AS 'OT Offered',
  employee.Senority
FROM site
  INNER JOIN employee
    ON site.siteID = employee.siteID
  LEFT OUTER JOIN ot_tracking
    ON ot_tracking.employee_ID = employee.employee_ID
WHERE employee.siteID = 1
AND employee.Department <> 'shop' AND Department = 'Log Yard' AND LogLoader = 'Yes' AND Active = 'Yes'
GROUP BY ot_tracking.operator,
         employee.Senority,
         employee.Department
ORDER BY employee.Department, `OT Offered`, employee.Senority

标签： mysql

解决方案

用括号括起来ot_tracking.operatorResponse = 'Yes' OR ot_tracking.operatorResponse = 'No'，因为运算符AND的优先级高于OR：

SUM(CASE WHEN ot_tracking.shiftDate BETWEEN '2020-12-01' AND '2020-12-31' AND (ot_tracking.operatorResponse = 'Yes' OR ot_tracking.operatorResponse = 'No') THEN ot_tracking.hours else 0 END) AS `OT Offered`

或使用 IN：

SUM(CASE WHEN ot_tracking.shiftDate BETWEEN '2020-12-01' AND '2020-12-31' AND ot_tracking.operatorResponse IN ('Yes', 'No') THEN ot_tracking.hours else 0 END) AS `OT Offered`

'

推荐阅读