代码之家 › 专栏 › 技术社区 › Ado Ren

如果在相邻小时内,则合并日期

mysql

Ado Ren · 技术社区 · 8 年前

我有一份日期和期限清单:

-------------------------------------
Date                 |    Duration 
-------------------------------------
2018-08-08 16:00:00  |   00:45:00
2018-08-08 17:00:00  |   00:45:00
2018-08-08 18:00:00  |   00:45:00
2018-08-08 20:00:00  |   00:45:00
-------------------------------------

所需的表格输出:

-------------------------------------
Date                 |    Duration 
-------------------------------------
2018-08-08 16:00:00  |   02:45:00
2018-08-08 20:00:00  |   00:45:00
-------------------------------------

有人能告诉我这个mysql查询吗?我的大脑迟钝了。

编辑1:

我现在将尝试选择匹配相邻查询:

SELECT * FROM appointments WHERE Date BETWEEN ADDTIME(Date, '00:30:00') AND ADDTIME(Date, '01:00:00');

编辑该字段than循环以查看是否提取更多行。

1 回复 | 直到 8 年前

Strawberry 8 年前

考虑一下。。。

CREATE TABLE my_table
    (date DATETIME NOT NULL PRIMARY KEY, duration TIME NOT NULL);


INSERT INTO my_table
    (date, duration)
VALUES
    ('2018-08-08 16:00:00', '00:45:00'),
    ('2018-08-08 17:00:00', '00:45:00'),
    ('2018-08-08 18:00:00', '00:45:00'),
    ('2018-08-08 20:00:00', '00:45:00')
;

SELECT MIN(date) date
     , SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) n
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev >= date - INTERVAL 1 HOUR THEN @i := @i ELSE @i:= @i+1 END i
            , @prev := date prev
         FROM my_table x
            , (SELECT @prev := null, @i:=0) vars
        ORDER
           BY date
     ) a
 GROUP 
    BY i;

+---------------------+----------+
| date                | n        |
+---------------------+----------+
| 2018-08-08 16:00:00 | 02:15:00 |
| 2018-08-08 20:00:00 | 00:45:00 |
+---------------------+----------+

http://sqlfiddle.com/#!9/55a1c2c/3