MySQL 按时间范围拆分 [英] MySQL split by time range

问题描述

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

I have a view which has two columns as below:

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

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

I'm trying to achieve the following with a single query:

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 字段将每一行拆分为多行.想知道这是否可行，或者我是否必须编写一个存储过程才能做到这一点.

In other words, I want to split each row into multiple rows based on the hour_from and hour_to fields. Was wondering if this is possible or if I have to write a stored procedure to do it.

推荐答案

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

You need a rowsource with all the times you want returned. Then you can use a JOIN operation to select the rows to be returned.

如果你有一张这样的桌子:

If you had a table like this:

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;
... 

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

Then you could use that in your query:

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函数:

If you want the time values in a specific format, you can use the DATE_FORMAT function:

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

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

This assumes that the values returned by the view are TIME datatype. If they aren't, you'll want to convert them. (Or you could get the same result working with character strings in a canonical format.)

注意:你有一张桌子不是强制性的；它可以是任何行源，例如内联视图.您只需要足够的行(在您的情况下，假设从 00:00 到 23:30 以半小时为增量，那就是 48 行.)

NOTE: It's not mandatory that you have a table; it can be any row source, such as an inline view. You just need enough rows (in your case, assuming half hour increments from 00:00 thru 23:30, thats 48 rows.)

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

If you have any time ranges that "cross" a midnight boundary (e.g. 22:00 to 02:00), the example query won't return any rows. You'd need to make adjustments.

类似于:

 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
          )
      )

这篇关于MySQL 按时间范围拆分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！