mysql中查询空行_MySQL GROUP BY并填充空行

小编典典

在此答案中，我将概述如何生成日历表。

创建三个表，分别表示天，小时和分钟：

CREATE TABLE days (

day DATE,

PRIMARY KEY (day)

)

CREATE TABLE hours (

hour INT,

PRIMARY KEY (hour)

)

CREATE TABLE minutes (

minute INT,

PRIMARY KEY (minute)

)

用0到23的数字填充小时表，用0到59的数字填充分钟表。要填充天表，可以创建如下过程：

CREATE PROCEDURE make_days(IN start_date DATE, IN end_date DATE)

BEGIN

DECLARE curr_date DATE;

SET curr_date = start_date;

WHILE curr_date <= end_date DO

INSERT IGNORE INTO days(day) VALUES(curr_date);

SET curr_date = DATE_ADD(curr_date, INTERVAL 1 DAY);

END WHILE;

END

然后，您可以调用此过程来创建如下日期：

CALL make_days('2011-01-01','2012-12-31');

现在，您可以使用类似于以下内容的查询为给定时间间隔中的每一分钟创建值：

SELECT YEAR(day) AS year, MONTH(day) AS month, DAYOFMONTH(day) AS day, hour, minute

FROM days, hours, minutes

WHERE CAST(CONCAT(day,' ',hour,':',minute) AS DATETIME) BETWEEN '2011-08-31 22:00' AND '2011-09-01 10:00'

ORDER BY year, month, day, hour, minute

2020-05-17