如何从两个不同的提供者中检索两个不同的时间间隔？

dfty9e19 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(347)

这里是我的问题：我有一个表，插入一些时间戳，我的商店得到每日送货员，但我有两个不同的供应商“提供商1”和“提供商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')

这就是我选择的提供商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

我必须对provider2做同样的事情，但是我需要有不同的时间间隔，并且两个查询都必须是单个的，而不是两个。下面是一个包含提供者\u 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   |
+-----------------+--------------------+---------------------+

sql mysql

来源：https://stackoverflow.com/questions/62755922/mysql-how-to-retrieve-two-distincts-time-intervals-from-two-different-provider

1条答案

按热度按时间

pbpqsu0x1#

创建一个包含所需时间的派生表。然后您可以加入：

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 .

赞(0）回复(0）举报 2021-07-24

我来回答

如何从两个不同的提供者中检索两个不同的时间间隔？

1条答案

相关问题

热门标签

最新问答