我在MySQL中查询得不到预期结果 [英] Not getting expected results with the query I've in MySQL

问题描述

我具有以下表结构，该表结构具有逐日班次.

I have the following table structure that has day wise shifts.

仅在 C 班次时，时间共享两天.

At only C shift, time is shared by two days.

您可以按 C 班次从 20:30 开始，到第二天的结束时间 06:00 .

As you can C Shift starts at 20:30 and ends the next's days 06:00.

表的结构和数据如下

create table `machine_shifts` (
   `date` date ,
   `shift_start_time` time ,
   `shift_end_time` time ,
   `shift` varchar (60),
   `updated_on` timestamp 
);

insert into `machine_shifts` (`date`, `shift_start_time`, `shift_end_time`, `shift`, `updated_on`) values('2010-01-01','06:00:00','14:30:00','A','2020-01-29 15:37:26'),
('2010-01-01','14:30:00','22:30:00','B','2020-01-29 15:37:26'),
('2010-01-01','22:30:00','06:00:00','C','2020-01-29 15:37:26'),
('2010-01-02','06:00:00','14:30:00','A','2020-01-29 15:37:26'),
('2010-01-02','14:30:00','22:30:00','B','2020-01-29 15:37:26'),
('2010-01-02','22:30:00','06:00:00','C','2020-01-29 15:37:26'),
('2010-01-03','06:00:00','14:30:00','A','2020-01-29 15:37:26'),
('2010-01-03','14:30:00','22:30:00','B','2020-01-29 15:37:26'),
('2010-01-03','22:30:00','06:00:00','C','2020-01-29 15:37:26'),
('2010-01-04','06:00:00','14:30:00','A','2020-01-29 15:37:26'),
('2010-01-04','14:30:00','22:30:00','B','2020-01-29 15:37:26'),
('2010-01-04','22:30:00','06:00:00','C','2020-01-29 15:37:27');

数据表示如下

现在我想要的是，我想检索两个给定时间轴之间的数据，其中包括上述数据集的偏移.我有以下查询

Now what I wanted is, I want to retrieve the data between two given timelines which includes shift from the above data set. And I've this following query

SELECT * FROM machine_shifts
WHERE ((CONCAT(`date`, ' ', shift_start_time))
   BETWEEN '2010-01-02 00:00:00' AND '2010-01-03 10:00:00')
OR   (CONCAT(`date`, ' ', shift_end_time)
   BETWEEN '2010-01-02 00:00:00' AND '2010-01-03 10:00:00') 
ORDER BY `date`, shift_start_time ASC

通过上述查询，我​​得到的是

With the above query, what I'm getting is

在上面获取的结果集中，最后一行不是预期的，也不应显示.即使我通过了2010-01，我也想在第一行中检索2010年1月1日的C偏移-02 00:00:00，因为它应该属于2010年1月1日的C转换.
这是我期望的以下结果

In the above fetched result set, the last row wasn't expected and shouldn't be displayed also I want to retrieve C shift of 2010-01-01 too in the first row even though I'm passing 2010-01-02 00:00:00 as it should fall under C shift of 2010-01-01.
This is the following result I'm expecting

我该如何实现?

推荐答案

您的查询看起来不错；您只需要一点条件逻辑即可调整班次C结束的结束日期.

Your query looks fine; you just need a little bit of conditional logic to adjust the end date for the ends of shift C.

这应该做到:

SELECT * FROM machine_shifts
WHERE 
    CONCAT(`date`, ' ', shift_start_time)
        BETWEEN '2010-01-02 00:00:00' 
        AND '2010-01-03 10:00:00')
    OR CONCAT(`date`, ' ', shift_end_time) + INTERVAL (shift = 'C') DAY
        BETWEEN '2010-01-02 00:00:00' 
        AND '2010-01-03 10:00:00'
ORDER BY `date`, shift_start_time ASC

这个想法是当我们遇到C班结束时，将一天增加到计算中.这就是 + INTERVAL(shift ='C')DAY 表达式的作用.

The idea is to add one day to the computation when we met the end of a C shift. This is what expression + INTERVAL (shift = 'C') DAY does.

请注意，此处不需要条件周围的嵌套括号.

Note that the nested parenthses around conditions are not needed here.

这篇关于我在MySQL中查询得不到预期结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！