这是我的问题:我有一个表，其中插入了一些时间戳，我的商店获得每日递送，但我有两个不同的提供者“provider_1”和“provider_2”，这是一个常规，所以他们必须满足最后期限。 但问题是provider_1例程与provider_2不同，我试图将它带到一个查询中，以显示它们是否按计划进行。 到目前为止，这是我自己设法做到的:
CREATE TABLE deliveries (
provider_Id INT NOT NULL AUTO_INCREMENT,
provider_name VARCHAR(50) NOT NULL,
deliver_time DATETIME NOT NULL,
finished_delivered DATETIME NOT NULL,
started_new_deliver DATETIME NOT NULL,
PRIMARY KEY(provider_Id)
)
;
-- data
INSERT INTO deliveries
(provider_name, deliver_time, finished_delivered, started_new_deliver)
VALUES
('Provider_1', '2020-05-16 4:59:59','2020-02-16 8:59:00' ,'2020-02-16 11:32:00'
('Provider_2','2020-05-16 8:31:59', '2020-02-16 11:50:59', '2020-02-16 12:35:00'),
('Provider_1', '2020-02-17 4:54:59', '2020-02-17 7:45:09','2020-02-17 11:03:09'),
('Provider_2','2020-02-17 10:00:59', '2020-02-17 12:40:59','2020-02-17 14:06:39'),
('Provider_1','2020-02-18 7:00:59', '2020-02-18 10:28:59', '2020-02-18 14:36:39')
这是我对Provider_1的选择:
SELECT
IF(deliveries.deliver_time IS NULL,NULL,IF(TIME(deliveries.deliver_time) BETWEEN '3:59:59' AND '5:59:59','1','0')) as delivered,
IF(deliveries.finished_delivered IS NULL,NULL,IF(TIME(deliveries.finished_delivered) BETWEEN '4:59:59' AND '6:59:59' ,'1','0')) as finished_delivered,
IF(deliveries.started_new_deliver IS NULL,NULL,IF(TIME(deliveries.started_new_deliver) BETWEEN '7:29:59' AND '9:29:59','1','0')) as started_new_deliver
FROM deliveries
WHERE deliveries.provider_name LIKE "Provider_1"
ORDER BY deliveries.provider_Id DESC
我必须对Provider_2做同样的事情，但是我需要有不同的时间间隔，并且两个查询都必须是一个，而不是两个。 下面是一个包含Provider_2间隔的表格:
+-----------------+--------------------+---------------------+
| deliver_time | finished_delivered | started_new_deliver |
+-----------------+--------------------+---------------------+
| 7:59:59-9:59:59 | 9:59:59-11:59:59 | 11:59:59-13:59:59 |
+-----------------+--------------------+---------------------+
创建具有所需时间的派生表。 然后，您可以将其加入到:
SELECT (TIME(d.deliver_time) BETWEEN p.t1 AND p.t2) as delivered,
(TIME(d.finished_delivered) BETWEEN p.t3 AND p.t4) as finished_delivered,
(TIME(d.started_new_deliver) BETWEEN p.t5 AND p.t6),'1','0')) as started_new_deliver
FROM (SELECT 'Provider_1' as provider_name, '3:59:59' as t1, '5:59:59' as t2, '4:59:59' as t3, '6:59:59' as t4, '7:29:59' as t5, '9:29:59' as t6
) p JOIN
deliveries d
ON d.provider_name = p.provider_name
ORDER BY d.provider_Id DESC
对于第二个提供程序，如下所示:
FROM (SELECT 'Provider_1' as provider_name, '3:59:59' as t1, '5:59:59' as t2, '4:59:59' as t3, '6:59:59' as t4, '7:29:59' as t5, '9:29:59' as t6
UNION ALL
SELECT 'Provider_2' as provider_name, '3:59:59' as t1, '5:59:59' as t2, '4:59:59' as t3, '6:59:59' as t4, '7:29:59' as t5, '9:29:59' as t6
) p JOIN
但你会根据自己的需要调整时间。
注意，我简化了标志的逻辑。 此逻辑将0/1作为数字返回，如果列为NULL，则返回NULL。
