作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我的 table :
CREATE TABLE `tbdata` (
`ID` INT(10) NOT NULL AUTO_INCREMENT,
`PatientID` INT(10) NOT NULL,
`RecordDate` DATETIME NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
表格中的数据如下所示:
ID PatientID RecordDate
132 172116 2019—11—15 13:43:45
133 172816 2019—11—15 13:43:55
134 172816 2019—11—15 13:44:06
135 172816 2019—11—15 13:44:16
136 172816 2019—11—15 13:44:27
137 172816 2019—11—15 13:44:38
138 172816 2019—11—15 13:44:48
139 172816 2019—11—15 13:44:59
140 172816 2019—11—15 13:45:09
141 172816 2019—11—15 13:45:20
142 172816 2019—11—15 13:45:31
143 172816 2019—11—15 13:45:41
144 172816 2019—11—15 13:45:52
145 172816 2019—11—15 13:46:02
146 172816 2019—11—15 13:46:13
147 172816 2019—11—15 13:46:24
148 172816 2019—11—15 13:46:35
149 172816 2019—11—15 13:46:46
150 172816 2019—11—15 13:46:56
151 172816 2019—11—15 13:47:07
152 172816 2019—11—15 13:47:19
153 172816 2019—11—15 13:47:30
154 172816 2019—11—15 13:47:43
155 172816 2019—11—15 13:47:54
156 172816 2019—11—15 13:48:04
157 172816 2019—11—15 13:48:15
158 172816 2019—11—15 14:48:25
159 172816 2019—11—15 17:48:36
160 172816 2019—11—15 18:48:47
161 172816 2019—11—15 20:48:57
162 172816 2019—11—15 22:49:08
163 172816 2019—11—15 23:49:18
所需结果如:
4 Hours slot :
start from min(RecordDate)
2019-11-15 13:00:00 - 2019-11-15 17:00:00
2019-11-15 17:00:00 - 2019-11-15 21:00:00
2019-11-15 21:00:00 - 2019-11-16 01:00:00
... till max(RecordDate)
最佳答案
我不知道它的表现如何,但它确实返回了你想要的:
编辑我已更新它以过滤掉表中没有数据的间隔。
**Schema (MySQL v8.0)**
CREATE TABLE `tbdata` (
`ID` INT(10) NOT NULL AUTO_INCREMENT,
`PatientID` INT(10) NOT NULL,
`RecordDate` DATETIME NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
INSERT INTO tbdata (PatientID, RecordDate)
VALUES
(172116, '2019-11-15 13:43:45'),
(172816, '2019-11-15 13:43:55'),
(172816, '2019-11-15 13:44:06'),
(172816, '2019-11-15 13:44:16'),
(172816, '2019-11-15 13:44:27'),
(172816, '2019-11-15 13:44:38'),
(172816, '2019-11-15 13:44:48'),
(172816, '2019-11-15 13:44:59'),
(172816, '2019-11-15 13:45:09'),
(172816, '2019-11-15 13:45:20'),
(172816, '2019-11-15 13:45:31'),
(172816, '2019-11-15 13:45:41'),
(172816, '2019-11-15 13:45:52'),
(172816, '2019-11-15 13:46:02'),
(172816, '2019-11-15 13:46:13'),
(172816, '2019-11-15 13:46:24'),
(172816, '2019-11-15 13:46:35'),
(172816, '2019-11-15 13:46:46'),
(172816, '2019-11-15 13:46:56'),
(172816, '2019-11-15 13:47:07'),
(172816, '2019-11-15 13:47:19'),
(172816, '2019-11-15 13:47:30'),
(172816, '2019-11-15 13:47:43'),
(172816, '2019-11-15 13:47:54'),
(172816, '2019-11-15 13:48:04'),
(172816, '2019-11-15 13:48:15'),
(172816, '2019-11-15 14:48:25'),
(172816, '2019-11-15 17:48:36'),
(172816, '2019-11-15 18:48:47'),
(172816, '2019-11-15 20:48:57'),
(172816, '2019-11-15 22:49:08'),
(172816, '2019-11-15 23:49:18');
DELIMITER //
CREATE PROCEDURE While_Loop()
BEGIN
SET @row_number = 0;
SELECT MIN(RecordDate), MAX(RecordDate)
INTO @min, @max
FROM tbdata;
SET @min = DATE_FORMAT(@min, "%Y-%m-%d %H:00:00");
SELECT CEIL(TIMESTAMPDIFF(HOUR, @min, @max)/4) INTO @number_of_intervals;
WHILE @row_number <= @number_of_intervals DO
SET @start_time = @min;
SET @end_time = DATE_ADD(@start_time, INTERVAL 4 HOUR);
SET @min = @end_time;
SET @row_number = @row_number + 1;
SELECT COUNT(PatientID) INTO @existent_slot FROM tbdata WHERE RecordDate BETWEEN @start_time and @end_time;
IF @existent_slot THEN
SELECT @start_time start_time, @end_time end_time;
END IF;
END WHILE;
END;
**Query #1**
CALL While_Loop();
| start_time | end_time |
| ------------------- | ------------------- |
| 2019-11-15 13:00:00 | 2019-11-15 17:00:00 |
| start_time | end_time |
| ------------------- | ------------------- |
| 2019-11-15 17:00:00 | 2019-11-15 21:00:00 |
| start_time | end_time |
| ------------------- | ------------------- |
| 2019-11-15 21:00:00 | 2019-11-16 01:00:00 |
您可能应该将结果存储在临时表中,而不是仅仅打印出来。
下面更新的代码对我有用。
DELIMITER $$
DROP PROCEDURE IF EXISTS `While_Loop`$$
CREATE PROCEDURE `While_Loop`()
BEGIN
DROP TEMPORARY TABLE IF EXISTS _tempTableTimeSlot;
CREATE TEMPORARY TABLE _tempTableTimeSlot(
starttime VARCHAR(100),
endtime VARCHAR(100)
);
SET @row_number = 0;
SELECT MIN(RecordDate), MAX(RecordDate)
INTO @min, @max
FROM tbdata;
SET @min = DATE_FORMAT(@min, "%Y-%m-%d %H:00:00");
SELECT CEIL(TIMESTAMPDIFF(HOUR, @min, @max)/4) INTO @number_of_intervals;
WHILE @row_number <= @number_of_intervals DO
SET @start_time = @min;
SET @end_time = DATE_ADD(@start_time, INTERVAL 4 HOUR);
SET @min = @end_time;
SET @row_number = @row_number + 1;
SELECT COUNT(PatientID) INTO @existent_slot FROM tbdata WHERE RecordDate BETWEEN @start_time AND @end_time;
IF @existent_slot THEN
INSERT INTO _tempTableTimeSlot(starttime,endtime) SELECT @start_time start_time1, @end_time end_time1;
END IF;
END WHILE;
SELECT * FROM _tempTable;
DROP TEMPORARY TABLE IF EXISTS _tempTableTimeSlot;
END$$
DELIMITER ;
关于mysql - 如何在 MySQL 服务器中将最小(日期)和最大(日期)之间的小时分割为 4 小时板,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/58873608/
我是一名优秀的程序员,十分优秀!