where 子句中的 Mysql 条件未正确报告

Question

我有以下 Mysql 查询，除了 Sum 部分外，它运行良好。我得到的结果是 Sum_IO 和 Sum_NIO 是在整个表上计算的，就好像标准部分一样

Cast(oee_main_interim.TS 作为日期) = CurDate()

被忽略了。我想要的是查询只会对属于今天日期 (curdate()) 的值求和:

Select
  oee_main_interim.NAME As Machine_ID,
  oee_machinenames.Name As Name,
  voee_mach_shifts.curr_Shift As curr_Shift,
  Sum((oee_main_interim.Left_IO + oee_main_interim.Right_IO)) As Sum_IO,
  Sum((oee_main_interim.Left_NIO + oee_main_interim.Right_NIO)) As Sum_NIO
From
  (oee_main_interim Join
  voee_mach_shifts On voee_mach_shifts.ID = oee_main_interim.NAME) Join
  oee_machinenames On oee_machinenames.ID = oee_main_interim.NAME
Where
  Cast(oee_main_interim.TS As date) = CurDate() And
  oee_main_interim.Shift In (Select
    voee_mach_shifts.curr_Shift
  From
    voee_mach_shifts Join
    oee_machinenames On oee_machinenames.ID = voee_mach_shifts.ID
  Group By
    voee_mach_shifts.curr_Shift, oee_machinenames.ID
  Order By
    oee_machinenames.ID)
Group By
  oee_main_interim.NAME, oee_machinenames.Name, voee_mach_shifts.curr_Shift
Order By
  oee_system.oee_main_interim.NAME

Answer 1

您可以使用 DATE(yourfield)=CURDATE() 仅匹配时间戳或日期时间字段的日期部分。