腾讯云

开发者社区

文档建议反馈控制台

首页

文章/答案/技术大牛

发布

社区首页 >问答首页 >获取24小时内的记录

问获取24小时内的记录
EN

Stack Overflow用户

提问于 2019-07-17 06:34:44

回答 2查看 174关注 0票数 0

我试着根据时间和时间在我的桌子上找到价格。例如，这是我的桌子

+----+-----------------+------------+----------+---------+
| id | subscription_id | start_time | end_time | pricing |
+----+-----------------+------------+----------+---------+
|  1 |               1 | 22:00:00   | 21:59:59 |     1.5 |
|  2 |               2 | 05:30:00   | 16:29:00 |    0.75 |
|  3 |               2 | 16:30:00   | 18:59:59 |    0.50 |
|  4 |               2 | 19:00:00   | 05:29:59 |     1.5 |
+----+-----------------+------------+----------+---------+

现在，我必须找到从16:00开始，到20:00结束的第二代记述--我尝试了几种类似的方法，但都没有成功

SET @startTime := '17:30:00';
SET @endTime := '20:30:00';

SELECT *
FROM subscription_detail_pricing sdp
WHERE (
    (start_time < end_time AND TIME(@startTime) BETWEEN start_time AND end_time)
    OR
    (start_time > end_time AND TIME(@startTime) NOT BETWEEN end_time AND start_time)
    OR
    (start_time < end_time AND TIME(@endTime) BETWEEN start_time AND end_time) 
    OR
    (start_time > end_time AND TIME(@endTime) NOT BETWEEN end_time AND start_time)
) AND subscription_id = 2;

我找不到与此范围相对应的3条记录的查询方法

如果有人能给我些建议，我就会失去自我--

谢谢你阅读BenWa

mysql

单节点MySQL

低成本高体验，解决您的基础业务数据需求

回答 2

Stack Overflow用户

回答已采纳

发布于 2019-07-17 07:38:49

这看起来像是一个典型的范围重叠查询。

如果可能的话，您可以为自己省去麻烦，将覆盖在"00:00:00"上的任何范围分割成两个单独的行，并使end_time成为独占的。

+----+-----------------+------------+----------+---------+
| id | subscription_id | start_time | end_time | pricing |
+----+-----------------+------------+----------+---------+
|  1 |               1 | 00:00:00   | 24:00:00 |     1.5 |
|  2 |               2 | 00:00:00   | 05:30:00 |     1.5 |
|  3 |               2 | 05:30:00   | 16:30:00 |    0.75 |
|  4 |               2 | 16:30:00   | 19:00:00 |    0.50 |
|  5 |               2 | 19:00:00   | 24:00:00 |     1.5 |
+----+-----------------+------------+----------+---------+

那么查询就变得简单多了。

SELECT *

  FROM subscription_detail_pricing

 WHERE start_time < "20:00:00"
   AND end_time > "16:00:00"

注意到，对于这个查询，我更喜欢独占比较，<和>，因为您正在比较

start_time < "20:00:00" -比较start_time (包括)和输入范围"20:00:00" (独占)的末尾
end_time > "16:00:00" --将end_time (独占)与输入范围"16:00:00" (包括在内)的开头进行比较

只要被比较的值中至少有一个是排他性的，检查相等性( <=或>= )对我来说就没有意义。

票数 1

Stack Overflow用户

发布于 2019-07-17 07:54:51

若要检查时间范围中的重叠，请将s和e作为定义范围的开始和结束，并将b和t作为我们要比较的范围的开始和终止，通常是这样的情况。

 b <= e AND t > s

就足以检查是否有重叠。但这只适用于b<t和s<e。在所给出的场景中，在id=4 ( s，start_time)之前，使用e (end_time)的"wrap“行s( row )有一个折痕。

解决问题的一种方法是只存储start_time小于end_time的行。

我们可以通过将行id=4分成两行来实现这一点。

+----+-----------------+------------+----------+---------+
| id | subscription_id | start_time | end_time | pricing |
+----+-----------------+------------+----------+---------+
|  4 |               2 | 19:00:00   | 24:00:00 |     1.5 |
|  5 |               2 | 00:00:00   | 05:30:00 |     1.5 |
+----+-----------------+------------+----------+---------+

类似地，行id=1可以指定为

|  1 |               1 | 00:00:00   | 24:00:00 |     1.5 |

或者，如果有什么原因，我们需要22:00开始，我们可以把它分成两个范围

| 1a  |              1 | 22:00:00   | 24:00:00 |     1.5 |
| 1b  |              1 | 00:00:00   | 22:00:00 |     1.5 |

按照这一办法：

SET @bt := '16:00:00' ;
SET @tt := '20:00:00' ;

SELECT sdp.id
     , sdp.start_time
     , sdp.end_time
     , sdp.pricing
     , ( TIME(@bt) <= sdp.end_time AND TIME(@tt) > sdp.start_time ) AS overlap
  FROM sdp
 WHERE sdp.subscription_id = 2

  id  start_time  end_time   pricing  overlap  
----  ----------  ---------  -------  -------
   2  05:30:00.0  16:30:00.0    0.75        1
   3  16:30:00.0  19:00:00.0    0.50        1
   4  19:00:00.0  24:00:00.0    1.50        1
   5  00:00:00.0  05:30:00.0    1.50        0

向后/包装范围重叠

如果我们必须在end_time之前处理向后的范围重叠，那么我们似乎可以否定用于前向检查的相同条件，如果范围是向前的还是向后的，则添加检查。

SELECT sdp.id
     , sdp.start_time
     , sdp.end_time
     , sdp.pricing
     ,     (sdp.start_time < sdp.end_time AND ( TIME(@bt) <= sdp.end_time AND TIME(@tt) > sdp.start_time )) AS f_overlap
     , NOT (sdp.start_time < sdp.end_time AND ( TIME(@bt) <= sdp.end_time AND TIME(@tt) > sdp.start_time )) AS b_overlap
  FROM sdp
 WHERE sdp.subscription_id = 2

中所示的行(将end_time稍微调整为范围之外的第一秒钟).

  id  start_time  end_time    pricing  f_overlap  b_overlap  
----  ----------  ----------  -------  ---------  ---------
   2  05:30:00.0  16:30:00.0  0.75             1          0
   3  16:30:00.0  19:00:00.0  0.50             1          0
   4  19:00:00.0  05:30:00.0  1.50             0          1

我们想要测试的条件包括在选择列表中，这使我们能够更好地看到正在发生的事情。我们还可以在同一行中返回@bt和@tt。

一旦我们测试了这些条件，我们就可以将它们转换为WHERE子句。

我们需要OR这两个条件，因为如果这两个条件都是真的，我们就会有重叠。

SELECT sdp.id
     , sdp.start_time
     , sdp.end_time
     , sdp.pricing
  FROM sdp
 WHERE sdp.subscription_id = 2
   AND (     (sdp.start_time < sdp.end_time AND ( TIME(@bt) <= sdp.end_time AND TIME(@tt) > sdp.start_time ))
      OR NOT (sdp.start_time < sdp.end_time AND ( TIME(@bt) <= sdp.end_time AND TIME(@tt) > sdp.start_time ))
       )

注:这些条件还没有经过充分的测试。请注意，这些条件要求@bt位于@et之前。如果我们想要处理那些“向后包装”，那么我们需要额外的条件检查和条件否定来处理这个问题。我们还希望确保测试范围的@bt为00:00和@et在24:00或之后。

用于安装：

CREATE TABLE sdp (id INT, subscription_id INT, start_time TIME(1), end_time TIME(1), pricing DECIMAL(8,2), PRIMARY KEY (id))
;
INSERT INTO sdp (id, subscription_id, start_time, end_time, pricing) VALUES 
 ( 1 , 1 , '22:00:00'   , '22:00:00' ,  1.50 )
,( 2 , 2 , '05:30:00'   , '16:30:00' ,  0.75 )
,( 3 , 2 , '16:30:00'   , '19:00:00' ,  0.50 )
,( 4 , 2 , '19:00:00'   , '05:30:00' ,  1.50 )
;

若要避免在范围中包装，请将所有范围设置为end_time后的start_time

DELETE FROM sdp WHERE id = 4; 
INSERT INTO sdp (id, subscription_id, start_time, end_time, pricing) VALUES 
 ( 4 , 2 , '19:00:00'   , '24:30:00' ,  1.50 )
,( 5 , 2 , '00:00:00'   , '05:30:00' ,  1.50 )
;