按时间范围分裂的MySQL

Question

我有一个视图，其中有两栏如下：

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

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

如果你有一张这样的桌子

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_

这假设视图返回的值是TIME数据类型。如果他们不是，你会想要转换他们。(或者您可以获得相同的结果，使用规范格式的字符串。)

注意:不是必须有一个表；它可以是任何行源，例如内联视图。您只需要足够的行(在您的例子中，假设从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
          )
      )