按DATETIME的范围分组-MySQL [英] Group by range of DATETIME - MySql

问题描述

好吧，我有一个表，其中包含一些重复的列，但是时间并不完全相同，我需要做一个小组，范围为2小时。

Well, I have a table with some columns that some of these are repeated, but the time is not exactly the same, I need to do a group by that has a range of 2 hours.

请注意，所有标有黑色的字段都会重复，但是datetime字段可能会相差几分钟，因此我需要在两个小时的间隔内加入所有人，组成一个小组。

Notice that all the fields marked in black repeat, but the datetime field may differ by a few minutes, so I need to make a group by joining everyone within that two-hour interval.

它类似于：选择* FROM tabela GROUP BY sinistro，prefixo，数据 AND horaIfull_oco范围2h

列是否必须带有其所有ID （如果可能），例如： id1，id2 等等等

And also in the result it is necessary that the columns come with all their ids if possible, eg: id1, id2 etc etc

他将加入所有相距最多两个小时的人。
我已经研究过，什么也没发现，如果有人知道怎么做。

He would join all those who had a maximum of two hours apart. I have already researched and found nothing, if anyone knows how to do.

推荐答案

假设数据库表中有一个具有以下模式的表：

Assuming a database table having a table with the following schema:

CREATE TABLE t1 (
    c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c2 VARCHAR(100),
    c3 VARCHAR(100),
    c4 DATE,
    c5 TIME,
    c6 DATETIME);

INSERT INTO t1 (c1,c2,c3,c4,c5,c6) 
VALUES 
(1,'DDA1123_12112018','55307','2018-12-11','00:43:00', '2018-12-11 00:43:00'),
(2,'DDA1123_12112018','55307','2018-12-11','00:07:00', '2018-12-11 00:07:00'),
(3,'DDA1124_12112018','55308','2018-12-11','03:07:00', '2018-12-11 03:07:00'),
(4,'DDA1124_12112018','55308','2018-12-11','04:07:00', '2018-12-11 04:07:00'),
(5,'DDA1124_12112018','55308','2018-12-11','07:07:00', '2018-12-11 07:07:00'),
(6,'DDA1125_12112018','55309','2018-12-11','06:07:00', '2018-12-11 06:07:00'),
(7,'DDA1125_12112018','55309','2018-12-11','07:57:00', '2018-12-11 07:57:00'),
(8,'DDA1126_12112018','55310','2018-12-11','08:07:00', '2018-12-11 08:07:00'),
(9,'DDA1127_12112018','55311','2018-12-11','12:07:00', '2018-12-11 12:07:00'),
(10,'DDA1127_12112018','55311','2018-12-11','14:07:00', '2018-12-11 14:07:00'),
(11,'DDA1127_12112018','55311','2018-12-11','17:07:00', '2018-12-11 17:07:00');

在这种情况下，您可以使用 CASE ... ...像本例查询一样，在 GROUP BY 中选择$ c>。

In that case you could use CASE ... WHEN in GROUP BY like in this example query.

SELECT c1,c2,c3,c4,c5,c6,
CASE TRUE
WHEN HOUR(c6) BETWEEN 0 AND 2 THEN 0 
WHEN HOUR(c6) BETWEEN 2 AND 4 THEN 2 
WHEN HOUR(c6) BETWEEN 4 AND 6 THEN 4 
WHEN HOUR(c6) BETWEEN 6 AND 8 THEN 6
WHEN HOUR(c6) BETWEEN 8 AND 10 THEN 8
WHEN HOUR(c6) BETWEEN 10 AND 12 THEN 10
WHEN HOUR(c6) BETWEEN 12 AND 14 THEN 12
WHEN HOUR(c6) BETWEEN 14 AND 16 THEN 14
WHEN HOUR(c6) BETWEEN 16 AND 18 THEN 16
WHEN HOUR(c6) BETWEEN 18 AND 20 THEN 18
WHEN HOUR(c6) BETWEEN 20 AND 22 THEN 20
WHEN HOUR(c6) BETWEEN 22 AND 24 THEN 22
END AS c7 FROM t1
GROUP BY c7;

您可以在 SQL小提琴。

编辑

为获得更好的结果，可以重写以上查询：

For better results the above query could be re-written:

SELECT c1,c2,c3,c4,c5,c6,
CASE TRUE
WHEN HOUR(c6) BETWEEN 0 AND 1 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 0 
WHEN HOUR(c6) BETWEEN 2 AND 3 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 2 
WHEN HOUR(c6) BETWEEN 4 AND 5 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 4 
WHEN HOUR(c6) BETWEEN 6 AND 7 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 6
WHEN HOUR(c6) BETWEEN 8 AND 9 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 8
WHEN HOUR(c6) BETWEEN 10 AND 11 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 10
WHEN HOUR(c6) BETWEEN 12 AND 13 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 12
WHEN HOUR(c6) BETWEEN 14 AND 15 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 14
WHEN HOUR(c6) BETWEEN 16 AND 17 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 16
WHEN HOUR(c6) BETWEEN 18 AND 19 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 18
WHEN HOUR(c6) BETWEEN 20 AND 21 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 20
WHEN HOUR(c6) BETWEEN 22 AND 23 AND MINUTE(c6) BETWEEN 0 AND 59 THEN 22
END AS c7 FROM t1
GROUP BY c7;

您可以在此 SQL小提琴。

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