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

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()仅匹配时间戳或日期时间字段的日期部分。