MySQL按时间范围分割

Question

我有一个包含两列的 View ，如下所示:

id    hour_from    hour_to
1     12:00        20:00
2     09:00        13:00
3     07:30        19:00
....................

我正在尝试通过单个查询实现以下目标:

id    hour
1     12:00        
1     12:30        
1     13:00        
1     13:30       
............
1     19:30        
2     09:00        
2     09:30      
2     10:00       
2     10:30   
............
2     12:30   
............

换句话说，我想根据 hour_from 和 hour_to 字段将每一行拆分为多行。想知道这是否可行，或者我是否必须编写一个存储过程来执行此操作。

Answer 1

您需要一个包含您想要返回的所有时间的行源。然后您可以使用 JOIN 操作来选择要返回的行。

如果你有这样一张 table :

CREATE TABLE cal (tm TIME NOT NULL PRIMARY KEY) ENGINE=InnoDB ;
INSERT INTO cal (tm)  VALUES ('00:00:00'),('00:30:00');
INSERT INTO cal (tm) SELECT ADDTIME(tm,'01:00:00') FROM cal;
INSERT INTO cal (tm) SELECT ADDTIME(tm,'02:00:00') FROM cal;
INSERT INTO cal (tm) SELECT ADDTIME(tm,'04:00:00') FROM cal;
... 

然后您可以在查询中使用它:

SELECT v.id
     , c.tm
  FROM myview v
  JOIN cal c
    ON c.tm >= v.hour_from
   AND c.tm <= v.hour_to
 ORDER BY v.id, c.tm

如果您想要特定格式的时间值，可以使用DATE_FORMAT 函数:

SELECT v.id
     , DATE_FORMAT(c.tm,'%H:%i') AS hour_

这假定 View 返回的值是 TIME 数据类型。如果不是，您将需要转换它们。 (或者您可以使用规范格式的字符串获得相同的结果。)

注意:您不必一定要有一张 table ；它可以是任何行源，例如内联 View 。您只需要足够的行(在您的情况下，假设从 00:00 到 23:30 增加半小时，即 48 行。)

如果您有任何“跨越”午夜边界的时间范围(例如 22:00 到 02:00)，示例查询将不会返回任何行。您需要做出调整。

类似于:

 JOIN cal c
   ON ( t.hour_from <= t.hour_to 
      AND c.tm >= v.hour_from
      AND c.tm <= v.hour_to
      )
   OR ( t.hour_from > t.hour_to 
      AND (   c.tm <= v.hour_from
          OR  c.tm >= v.hour_to
          )
      )