Mysql标准中的where子句没有正确报告

Question

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

Cast(oee_main_interim.TS As date) = 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()只匹配时间戳或datetime字段的日期部分