MYSQL SUM 函数 with and inside

Question

我正在执行查询以一次获取左侧导航的多个计数。我正在总结符合每个条件的行数，并将该数字传递给左侧导航的 DOM，但是其中两个 SUMS 比其他的更复杂并且返回 0，即使我知道数据库中有行匹配表达式。

$processingcounts = $second->select(DB::raw(' 
        SUM(scheduled_in != "") as processingPreRepairs,
        SUM(in_arrival = 1) as processingNewArrivals, 
        SUM(delivery_complete = 1) as processingDelivered, 
        SUM(supplement = 1) as processingSupplement, 
        SUM(scheduled_in >= '.$start.' and scheduled_in <= '.$end.') as processingCurrentWeek, 
        SUM(scheduled_in >= '.$nextStart.' and scheduled_in <= '.$nextEnd.') as processingNextWeek, 
        SUM(file_location = "SCHEDULED") as processingScheduledList, 
        SUM(file_location = "IN-SHOP") as processingInShop, 
        SUM(file_location = "OFFICE") as processingOffice 
        '))->first();

所有其他 SUMS 都返回正确的值，但我称为“processingCurrentWeek”和“processingNextWeek”的两个 SUMS 返回 0，尽管它们应该分别返回 2 和 3。我的问题是，您能否像我在下面所做的那样使用 SUM，基本上其中包含它们。

编辑:

这是带有日期的查询输出，当我转储 laravel 查询时。

SUM(scheduled_in != "") as processingPreRepairs,
SUM(in_arrival = 1) as processingNewArrivals, 
SUM(delivery_complete = 1) as processingDelivered, 
SUM(supplement = 1) as processingSupplement, 
SUM(scheduled_in >= 2016-06-13 and scheduled_in <= 2016-06-17) as processingCurrentWeek, 
SUM(scheduled_in >= 2016-06-20 and scheduled_in <= 2016-06-24) as processingNextWeek, 
SUM(file_location = "SCHEDULED") as processingScheduledList, 
SUM(file_location = "IN-SHOP") as processingInShop, 
SUM(file_location = "OFFICE") as processingOffice 

Answer 1

日期必须包装成字符串。他们现在解释为表达式:2016-06-10 = 2000。

您可以使用占位符:

$processingcounts = $second->select(' 
    SUM(scheduled_in != "") as processingPreRepairs,
    SUM(in_arrival = 1) as processingNewArrivals, 
    SUM(delivery_complete = 1) as processingDelivered, 
    SUM(supplement = 1) as processingSupplement, 
    SUM(scheduled_in >= ? and scheduled_in <= ?) as processingCurrentWeek, 
    SUM(scheduled_in >= ? and scheduled_in <= ?) as processingNextWeek, 
    SUM(file_location = "SCHEDULED") as processingScheduledList, 
    SUM(file_location = "IN-SHOP") as processingInShop, 
    SUM(file_location = "OFFICE") as processingOffice 
    ', [$start, $end, $nextStart, $nextEnd])->first();