MySQL 查询在两个时间戳列之间搜索一系列发布日期记录

Question

请帮我解决这个问题，

我发布 2 个日期来搜索在特定日期进行维护并离开的车辆，然后将这些结果按时间范围划分。我的表格如下所示:

Ticket          VEhicle         Type            Model           Center          Depart          DateIn                      DateOut
________________________________________________________________________________________________________________________________________________
JBN-115         D113            MOTO BIKE       YBR125          254             403             2017-01-16 16:38:00         2017-01-25 08:54:00
JBN-122         S001            CAR             TYT HILUX       254             410             2017-01-17 08:39:00         2017-01-17 09:39:00
JBN-123         C003            MOTO BIKE       MAX100R         254             403             2017-01-16 09:50:00         2017-01-17 09:41:00
JBN-124         D313            MOTO BIKE       YBR125          254             403             2017-01-16 16:38:00         2017-01-25 08:54:00
JBN-125         S002            CAR             TYT HILUX       254             410             2017-01-17 12:39:00         2017-01-18 14:39:00
JBN-126         C053            MOTO BIKE       MAX100R         254             403             2017-01-16 08:41:00         2017-01-31 09:41:00
JBN-127         D133            MOTO BIKE       YBR125          254             403             2017-01-18 15:38:00         2017-01-25 08:54:00
JBN-128         S008            MOTO BIKE       TYT HILUX       254             410             2017-01-17 13:13:00         2017-01-20 09:24:00
JBN-129         C043            MOTO BIKE       MAX100R         254             403             2017-01-18 08:41:00         2017-01-31 09:41:00
JBN-122         S012            CAR             TYT HILUX       254             410             2017-01-17 08:50:00         2017-01-17 19:39:00
JBN-122         S062            MOTO BIKE       YBR125          254             410             2017-01-17 14:50:00         

因此，如果车辆在发布的日期范围内进行了维护，但仍未离开维护地点，即使退出日期超出了发布的日期，它也会显示在结果中。或者如果日期为空，它仍然会显示，因为它尚未离开该位置。

所以我正在运行查询以获取 2017-01-17 和 2017-01-18 之间的记录

查询应该寻找此车辆，因为您符合条件:

Ticket          VEhicle         Type            Model           Center          Depart          DateIn                      DateOut
________________________________________________________________________________________________________________________________________________
JBN-122         S001            CAR             TYT HILUX       254             410             2017-01-17 08:39:00         2017-01-17 09:39:00
JBN-122         S012            CAR             TYT HILUX       254             410             2017-01-17 08:50:00         2017-01-17 19:39:00
JBN-125         S002            CAR             TYT HILUX       254             410             2017-01-17 12:39:00         2017-01-18 14:39:00
JBN-128         S008            MOTO BIKE       TYT HILUX       254             410             2017-01-17 13:13:00         2017-01-20 14:24:00
JBN-122         S062            MOTO BIKE       YBR125          254             410             2017-01-17 14:50:00         

然后我希望看到这样的查询输出:

Type        8am     10am    12pm    2pm     4pm
_________________________________________________
CAR         2       1       2       2       2
MOTO BIKE   0       0       1       2       2

我已经尝试过这个:

select c.vtype, 
   ( select COUNT(vtype) from Availability where (DATE_FORMAT(DateIn, '%Y-%m-%d %H:%i') < '2017-01-17 00:00' AND DATE_FORMAT(DateOut, '%Y-%m-%d %H:%i') < '2017-01-18 10:00') AND costCente = '254' AND vtype =c.vtype ) as Eight,
   ( select COUNT(vtype) from Availability where (DATE_FORMAT(DateIn, '%Y-%m-%d %H:%i') < '2017-01-17 10:00' AND DATE_FORMAT(DateOut, '%Y-%m-%d %H:%i') < '2017-01-18 12:00') AND  costCente = '254' AND vtype =c.vtype ) as Ten,
   ( select COUNT(vtype) from Availability where (DATE_FORMAT(DateIn, '%Y-%m-%d %H:%i') < '2017-01-17 12:00' AND DATE_FORMAT(DateOut, '%Y-%m-%d %H:%i') < '2017-01-18 14:00') AND  costCente = '254' AND vtype =c.vtype ) as Twelve,
   ( select COUNT(vtype) from Availability where (DATE_FORMAT(DateIn, '%Y-%m-%d %H:%i') < '2017-01-17 14:00' AND DATE_FORMAT(DateOut, '%Y-%m-%d %H:%i') < '2017-01-18 16:00') AND  costCente = '254' AND vtype =c.vtype ) as Tow,
   ( select COUNT(vtype) from Availability where (DATE_FORMAT(DateIn, '%Y-%m-%d %H:%i') < '2017-01-17 16:00' AND DATE_FORMAT(DateOut, '%Y-%m-%d %H:%i') < '2017-01-18 19:00') AND  costCente = '254' AND vtype =c.vtype ) as Four
   from Availability c where costCente = '254' GROUP BY vtype ORDER BY vtype DESC

Answer 1

我不确定您的时间范围的条件是什么，例如上午 8 点:DateIn < '2017-01-17 00:00' 和 DateOut < '2017-01-18 10:00'，上午 8 点之间没有任何联系和这个。所以我认为上午 8 点是计算上午 0 点到 8 点之间仍在维护的车辆数量的范围时间

SET         @FromTime   = '2017-01-17 00:00:00',
            @ToTime     = '2017-01-18 23:59:59';

SELECT      Type,
            sum(if(DateIn between @FromTime and @FromTime+interval 8 hour,1,0)) as `8am`,
            sum(if(DateIn between @FromTime +interval 8 hour and @FromTime+interval 10 hour,1,0)) as `10am`,
            sum(if(DateIn between @FromTime +interval 10 hour and @FromTime+interval 12 hour,1,0)) as `12am`,
            sum(if(DateIn between @FromTime +interval 12 hour and @FromTime+interval 14 hour,1,0)) as `2pm`,
            sum(if(DateIn between @FromTime +interval 14 hour and @FromTime+interval 16 hour,1,0)) as `4pm`

FROM        Availability

WHERE       costCente = '254'
            AND DateIn >= @FromTime AND if(DateOut = '',DateIn,DateOut) <= @ToTime

GROUP BY    1 
ORDER BY    1 DESC;

您可以修改 if 中的条件以适合您的情况。