使用标准的MySQL函数，有没有一种方式可以编写一个查询来返回两个日期之间的天数列表。
例如，给定2009-01-01和2009-01-13，它将返回一个具有以下值的单列表：
2009-01-01
2009-01-02
2009-01-03
2009-01-04
2009-01-05
2009-01-06
2009-01-07
2009-01-08
2009-01-09
2009-01-10
2009-01-11
2009-01-12
2009-01-13
编辑：看起来我没有表达清楚。我想要生成这个列表。我在数据库中存储了值（按日期时间），但希望将它们聚合到上面的日期列表的左外连接中（我希望在某些连接的右侧获取 null，并会处理这些 null）。
SELECT CAST(DAYNAME(daydate) as CHAR) as dayname,daydate
FROM
(select CAST((date_add('20110101', interval H.i*100 + T.i*10 + U.i day) )as DATE) as daydate
from erp_integers as H
cross
join erp_integers as T
cross
join erp_integers as U
where date_add('20110101', interval H.i*100 + T.i*10 + U.i day ) <= '20110228'
order
by daydate ASC
)Days
SELECT *
FROM (
SELECT DATEADD(d, number - 1, '2009-01-01') AS dt
FROM Numbers
WHERE number BETWEEN 1 AND DATEDIFF(d, '2009-01-01', '2009-01-13') + 1
) AS DateRange
LEFT JOIN YourStuff
ON DateRange.dt = YourStuff.DateColumn
我看过一些与表值函数有关的变化。
你也可以保留一个永久的日期列表。我们的数据仓库中也有这样的列表，包括每天的时间。
以下示例显示一个月的日期范围 - 从第一天到同一月的最后一天。您可以通过更改@e `INTERVAL`值来调整日期范围。
SET @s := CAST('2021-12-01' AS DATE); -- start at 2021-12-01
SET @e := @s + INTERVAL 1 MONTH - INTERVAL 1 DAY; -- ends at 2021-12-31
WITH RECURSIVE d_range AS
(
SELECT @s AS gdate -- generated dates
UNION ALL
SELECT gdate + INTERVAL 1 day
FROM d_range
WHERE gdate < @e
)
SELECT *
FROM d_range;
结果：
| gdate |
+------------+
| 2021-12-01 |
| 2021-12-02 |
| 2021-12-03 |
| 2021-12-04 |
| 2021-12-05 |
| 2021-12-06 |
| 2021-12-07 |
| 2021-12-08 |
| 2021-12-09 |
| 2021-12-10 |
| 2021-12-11 |
| 2021-12-12 |
| 2021-12-13 |
| 2021-12-14 |
| 2021-12-15 |
| 2021-12-16 |
| 2021-12-17 |
| 2021-12-18 |
| 2021-12-19 |
| 2021-12-20 |
| 2021-12-21 |
| 2021-12-22 |
| 2021-12-23 |
| 2021-12-24 |
| 2021-12-25 |
| 2021-12-26 |
| 2021-12-27 |
| 2021-12-28 |
| 2021-12-29 |
| 2021-12-30 |
| 2021-12-31 |
+------------+
-> 31 rows in set (0.037 sec)
注意。此查询使用递归，因此速度较慢
31行(0.037秒)，选择更长的日期范围时，速度会变得更慢。
我已经和这个问题斗争了相当长时间了。由于这是我在谷歌上搜索解决方案时的第一个结果，让我发布一下我到目前为止所做的工作。
SET @d := '2011-09-01';
SELECT @d AS d, cast( @d := DATE_ADD( @d , INTERVAL 1 DAY ) AS DATE ) AS new_d
FROM [yourTable]
WHERE @d <= '2012-05-01';
请将[yourTable]替换为来自您的数据库的表。诀窍在于您选择的表中的行数必须大于等于您想要返回的日期数量。我尝试使用表占位符DUAL，但它只会返回一行。
CREATE PROCEDURE `get_amount_subscription_per_month`()
BEGIN
-- Select the ultimate start and enddate from subscribers
select @startdate := min(DATE_FORMAT(a.startdate, "%Y-%m-01")),
@enddate := max(DATE_FORMAT(a.enddate, "%Y-%m-01")) + interval 1 MONTH
from subscription a;
-- Tmp table with all months (dates), you can always format them with DATE_FORMAT)
DROP TABLE IF EXISTS tmp_months;
create temporary table tmp_months (
year_month date,
PRIMARY KEY (year_month)
);
set @tempDate=@startdate; #- interval 1 MONTH;
-- Insert every month in tmp table
WHILE @tempDate <= @enddate DO
insert into tmp_months (year_month) values (@tempDate);
set @tempDate = (date(@tempDate) + interval 1 MONTH);
END WHILE;
-- All months
select year_month from tmp_months;
-- If you want the amount of subscription per month else leave it out
select mnd.year_month, sum(subscription.amount) as subscription_amount
from tmp_months mnd
LEFT JOIN subscription ON mnd.year_month >= DATE_FORMAT(subscription.startdate, "%Y-%m-01") and mnd.year_month <= DATE_FORMAT(subscription.enddate, "%Y-%m-01")
GROUP BY mnd.year_month;
END
便携式解决方案
-- Generate
WITH
tdates AS (
WITH t19 AS (
SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union
select 7 union select 8 union select 9 )
SELECT adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) datei
FROM t19 AS t0, t19 AS t1, t19 AS t2, t19 AS t3, t19 AS t4
)
select * from tdates
where datei between '2012-02-10' and '2012-02-15'
在改进@brad的答案时，我使用了带有游标的CTE：
DECLARE cursorPeriod CURSOR FOR SELECT * FROM (
WITH RECURSIVE period as
(
SELECT '2019-01-01' as dt
UNION
SELECT DATE_ADD(period.dt, INTERVAL 1 DAY) FROM period WHERE DATE_ADD(period.dt, INTERVAL 1 DAY) <= '2019-04-30'
)
SELECT DATE(dt) FROM period ORDER BY dt ) AS derived_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
我会使用类似这样的东西：
DECLARE @DATEFROM AS DATETIME
DECLARE @DATETO AS DATETIME
DECLARE @HOLDER TABLE(DATE DATETIME)
SET @DATEFROM = '2010-08-10'
SET @DATETO = '2010-09-11'
INSERT INTO
@HOLDER
(DATE)
VALUES
(@DATEFROM)
WHILE @DATEFROM < @DATETO
BEGIN
SELECT @DATEFROM = DATEADD(D, 1, @DATEFROM)
INSERT
INTO
@HOLDER
(DATE)
VALUES
(@DATEFROM)
END
SELECT
DATE
FROM
@HOLDER
那么@HOLDER变量表将保存这两个日期之间每天递增的所有日期，准备随心所欲地连接。
DELIMITER $$
CREATE PROCEDURE popula_calendario_controle()
BEGIN
DECLARE a INT Default 0;
DECLARE first_day_of_year DATE;
set first_day_of_year = CONCAT(DATE_FORMAT(curdate(),'%Y'),'-01-01');
one_by_one: LOOP
IF dayofweek(adddate(first_day_of_year,a)) <> 1 THEN
INSERT INTO calendario.controle VALUES(null,150,adddate(first_day_of_year,a),adddate(first_day_of_year,a),1);
END IF;
SET a=a+1;
IF a=365 THEN
LEAVE one_by_one;
END IF;
END LOOP one_by_one;
END $$
这个过程将插入从年初到现在的所有日期，只需替换“开始”和“结束”的日期，您就可以开始了！
select * from table_name where col_Date between '2011/02/25' AND DATEADD(s,-1,DATEADD(d,1,'2011/02/27'))
在这里，首先将一天添加到当前的endDate，它将变为2011-02-28 00:00:00，然后减去一秒钟，使结束日期为2011-02-27 23:59:59。通过这样做，您可以获得给定时间间隔之间的所有日期。
输出：
2011/02/25
2011/02/26
2011/02/27