mysql - 选择两个日期之间的数据，排除某些天数

Question

我需要进行查询，选择两个日期之间的数据，但排除工作日和其他一些日子，例如公共(public)假期。

SELECT 
`tblproduction`.`OperaterID`, `tblproduction`.`OperationID`, 
SUM(`tblproduction`.`TotalProduced`) AS TotalProduced, 
SUM(`tblproduction`.`TotalProducedOperator`) AS TotalProducedOp,
'Normal working day' AS DayType
FROM `tblproduction` 
WHERE tblproduction.StartDateTime >= '2015-02-01 00:00:00' AND (tblproduction.StartDateTime <= '2015-02-28 23:59:59') AND tblproduction.OperaterID = 10
AND (DAYOFWEEK(tblproduction.StartDateTime) IN (1,7)) AND (DATE(tblproduction.StartDateTime) NOT IN (SELECT HolidayDate FROM tblholidays))
GROUP BY `tblproduction`.`OperaterID`, `tblproduction`.`OperationID`
UNION ALL
SELECT 
`tblproduction`.`OperaterID`, `tblproduction`.`OperationID`, 
SUM(`tblproduction`.`TotalProduced`) AS TotalProduced, 
SUM(`tblproduction`.`TotalProducedOperator`) AS TotalProducedOp,
'Weekend' AS DayType
FROM `tblproduction` 
WHERE tblproduction.StartDateTime >= '2015-02-01 00:00:00' AND (tblproduction.StartDateTime <= '2015-02-28 23:59:59') AND tblproduction.OperaterID = 10
AND (DAYOFWEEK(tblproduction.StartDateTime) NOT IN (1,7)) AND (DATE(tblproduction.StartDateTime) NOT IN (SELECT HolidayDate FROM tblholidays))
GROUP BY `tblproduction`.`OperaterID`, `tblproduction`.`OperationID`

为此，我有一个包含预定义日期的表，该表代表名为 tblHolidays 的公共(public)假期。 SQL 查询的标准之一是避免计算假期期间制作的件数。通过运行此查询，Steel 会包含假期日期。我应该在查询中更改什么？

Answer 1

也许从这个开始......

SELECT p.OperaterID
     , p.OperationID
     , SUM(p.TotalProduced) TotalProduced
     , SUM(p.TotalProducedOperator) TotalProducedOp
     , CASE WHEN DAYOFWEEK(p.startdatetime) IN (1,7) THEN 'Normal working day' ELSE 'Weekend' END DayType
  FROM tblproduction p
 WHERE p.StartDateTime >= '2015-02-01 00:00:00' AND p.StartDateTime <= '2015-02-28 23:59:59'
   AND p.OperaterID = 10
   AND DATE(p.StartDateTime) NOT IN (SELECT HolidayDate FROM tblholidays)
 GROUP 
    BY p.OperaterID
     , p.OperationID
     , CASE WHEN DAYOFWEEK(p.startdatetime) IN (1,7) THEN 'Normal working day' ELSE 'Weekend' END