blocks|key|1634736|text|创建一个带有两个参数a_begin和a_end的存储过程。在其中创建一个名为t的临时表，声明一个变量d，将a_begin赋值给d，然后运行一个WHILE循环INSERT+d+into+t并调用ADDDATE函数来递增值d。最后是SELECT+*+FROM+t。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1634737|entityMap^0|1Z|5|26|6|2O|7|36|F|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]|$9|M|A|N|B|C]|$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|G|$]]

Create a stored procedure which takes two parameters a_begin and a_end.
Create a temporary table within it called t, declare a variable d, assign a_begin to d, and run a <code>WHILE</code> loop <code>INSERT</code>ing d into t and calling <code>ADDDATE</code> function to increment the value d. Finally <code>SELECT * FROM t</code>.

blocks|key|1634825|text|您可以像这样使用MySQL的user+variables：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1634826|SET+@num+=+-1;
SELECT+DATE_ADD(+'2009-01-01',+interval+@num+:=+@num%2B1+day)+AS+date_sequence,+
your_table.*+FROM+your_table
WHERE+your_table.other_column+IS+NOT+NULL
HAVING+DATE_ADD('2009-01-01',+interval+@num+day)+<=+'2009-01-13'|code-block|syntax|javascript|1634827|@num是-1，因为您在第一次使用它时添加了它。此外，您不能使用"HAVING+date_sequence“，因为这会使user变量为每行增加两次。|1634828|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/user-variables.html^0|E|E|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

You can use MySQL's <a href="http://dev.mysql.com/doc/refman/5.1/en/user-variables.html" rel="noreferrer">user variables</a> like this:

<pre><code>SET @num = -1;
SELECT DATE_ADD( '2009-01-01', interval @num := @num+1 day) AS date_sequence, 
your_table.* FROM your_table
WHERE your_table.other_column IS NOT NULL
HAVING DATE_ADD('2009-01-01', interval @num day) &lt;= '2009-01-13'
</code></pre>

@num is -1 because you add to it the first time you use it. Also, you can't use "HAVING date_sequence" because that makes the user variable increment twice for each row.

blocks|key|1634904|text|借用this+answer中的想法，您可以设置一个从0到9的表，并使用该表生成日期列表。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1634905|CREATE+TABLE+num+(i+int);
INSERT+INTO+num+(i)+VALUES+(0),+(1),+(2),+(3),+(4),+(5),+(6),+(7),+(8),+(9);

select+adddate('2009-01-01',+numlist.id)+as+`date`+from
(SELECT+n1.i+%2B+n10.i*10+%2B+n100.i*100+AS+id
+++FROM+num+n1+cross+join+num+as+n10+cross+join+num+as+n100)+as+numlist
where+adddate('2009-01-01',+numlist.id)+<=+'2009-01-13';|code-block|syntax|javascript|1634906|这将允许您生成最多1000个日期的列表。如果需要进行更大规模的查询，可以向内部查询添加另一个交叉联接。|1634907|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/273623/mysql-select-from-a-list-of-numbers-those-without-a-counterpart-in-the-id-field/273703#273703^0|2|4|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

Borrowing an idea from <a href="https://stackoverflow.com/questions/273623/mysql-select-from-a-list-of-numbers-those-without-a-counterpart-in-the-id-field/273703#273703">this</a> answer, you can set up a table with 0 through 9 and use that to generate your list of dates.

<pre><code>CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

select adddate('2009-01-01', numlist.id) as `date` from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
 FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2009-01-01', numlist.id) &lt;= '2009-01-13';
</code></pre>

This will allow you to generate a list of up to 1000 dates. If you need to go larger, you can add another cross join to the inner query.

blocks|key|1420088|text|通常情况下，人们会使用一个辅助数字表，你通常会为这个目的保留一个辅助数字表，并对此进行一些变化：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1420089|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')+%2B+1
)+AS+DateRange
LEFT+JOIN+YourStuff
++++ON+DateRange.dt+=+YourStuff.DateColumn|code-block|syntax|javascript|1420090|我见过表值函数的变体，等等。|1420091|您还可以保留一个永久的日期列表。我们在数据仓库中有它以及一天中的时间列表。|1420092|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Typically one would use an auxiliary numbers table you usually keep around for just this purpose with some variation on this:

<pre><code>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
</code></pre>

I've seen variations with table-valued functions, etc.

You can also keep a permanent list of dates. We have that in our data warehouse as well as a list of times of day.

blocks|key|1634999|text|我会使用类似这样的东西：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1635000|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|code-block|syntax|javascript|1635001|然后，@HOLDER变量表保存这两个日期之间按天递增的所有日期，准备好按您的心情加入。|offset|length|style|CODE|1635002|entityMap^0|0|0|3|7|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I would use something similar to this:

<pre><code>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 &lt; @DATETO
BEGIN

 SELECT @DATEFROM = DATEADD(D, 1, @DATEFROM)
 INSERT 
 INTO
 @HOLDER
 (DATE)
 VALUES
 (@DATEFROM)
END

SELECT 
 DATE
FROM
 @HOLDER
</code></pre>

Then the <code>@HOLDER</code> Variable table holds all the dates incremented by day between those two dates, ready to join at your hearts content.

blocks|key|1635085|text|我们在我们的HRMS系统中使用了它，你会发现它很有用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1635086|SELECT+CAST(DAYNAME(daydate)+as+CHAR)+as+dayname,daydate
++++FROM
++++(select+CAST((date_add('20110101',+interval+H.i*100+%2B+T.i*10+%2B+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+%2B+T.i*10+%2B+U.i+day+)+<=+'20110228'
++++order
++++++++by+daydate+ASC
++++++++)Days|code-block|syntax|javascript|1635087|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

We used this in our HRMS System you will find it useful

<pre><code>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 ) &lt;= '20110228'
 order
 by daydate ASC
 )Days
</code></pre>

blocks|key|1420297|text|此解决方案适用于MySQL+5.0|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1420298|创建一个表-+mytable。|offset|length|style|CODE|1420299|模式并不重要。重要的是其中的行数。|1420300|因此，您可以只保留一个INT类型的列，其中有10行，值为-1到10。|1420301|SQL：|1420302|set+@tempDate=date('2011-07-01')+-+interval+1+day;
select
date(@tempDate+:=+(date(@tempDate)+%2B+interval+1+day))+as+theDate
from+mytable+x,mytable+y
group+by+theDate
having+theDate+<=+'2011-07-31';|code-block|syntax|javascript|1420303|限制:以上查询返回的最大日期数为|1420304|(rows+in+mytable)*(rows+in+mytable)+=+10*10+=+100.|1420305|您可以通过在sql中更改form+part来增加此范围：|1420306|来自mytable+x、mytable+y、mytable+z|1420307|因此，范围是10*10*10+=1000，依此类推。|1420308|entityMap^0|0|7|7|0|0|0|0|0|0|0|1E|0|0|0|6|E|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@$D|16|E|17|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|18|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|19|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1A|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|1B|8|@]|9|@]|A|$Q|R]]|$1|S|3|T|5|6|7|1C|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1D|8|@$D|1E|E|1F|F|G]]|9|@]|A|$]]|$1|W|3|X|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1H|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1I|8|@$D|1J|E|1K|F|G]]|9|@]|A|$]]|$1|12|3|-4|5|6|7|1L|8|@]|9|@]|A|$]]]|13|$]]

This solution is working with MySQL 5.0 
Create a table - <code>mytable</code>. 
The schema does not material. What matters is the number of rows in it. 
So, you can keep just one column of type INT with 10 rows, values - 1 to 10. 

SQL:

<pre><code>set @tempDate=date('2011-07-01') - interval 1 day;
select
date(@tempDate := (date(@tempDate) + interval 1 day)) as theDate
from mytable x,mytable y
group by theDate
having theDate &lt;= '2011-07-31';
</code></pre>

Limitation:
The maximum number of dates returned by above query will be 
<code>(rows in mytable)*(rows in mytable) = 10*10 = 100.</code> 

You can increase this range by changing form part in sql: 
from mytable x,mytable y, mytable z 
So, the range be <code>10*10*10 =1000</code> and so on.

blocks|key|1635207|text|CREATE+FUNCTION+[dbo].[_DATES]
(
++++@startDate+DATETIME,
++++@endDate+DATETIME
)
RETURNS+
@DATES+TABLE(
++++DATE1+DATETIME
)
AS
BEGIN
++++WHILE+@startDate+<=+@endDate
++++BEGIN+
++++++++INSERT+INTO+@DATES+(DATE1)
++++++++++++SELECT+@startDate+++
++++SELECT+@startDate+=+DATEADD(d,1,@startDate)+
++++END
RETURN
END|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1635208|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>CREATE FUNCTION [dbo].[_DATES]
(
 @startDate DATETIME,
 @endDate DATETIME
)
RETURNS 
@DATES TABLE(
 DATE1 DATETIME
)
AS
BEGIN
 WHILE @startDate &lt;= @endDate
 BEGIN 
 INSERT INTO @DATES (DATE1)
 SELECT @startDate 
 SELECT @startDate = DATEADD(d,1,@startDate) 
 END
RETURN
END
</code></pre>

blocks|key|1420439|text|我已经为此斗争了很长一段时间了。由于这是我搜索解决方案时在Google上的第一个命中，所以让我发布我到目前为止所得到的结果。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1420440|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';|code-block|syntax|javascript|1420441|将[yourTable]替换为数据库中的表。诀窍在于，您选择的表中的行数必须为>=，即您希望返回的日期数。我尝试使用表格占位符DUAL，但它只返回一行。|offset|length|style|CODE|1420442|entityMap^0|0|0|1|B|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I've been fighting with this for quite a while. Since this is the first hit on Google when I searched for the solution, let me post where I've gotten so far.

<pre><code>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 &lt;= '2012-05-01';
</code></pre>

Replace <code>[yourTable]</code> with a table from your database. The trick is that the number of rows in the table you select must be >= the number of dates you want to be returned. I tried using the table placeholder DUAL, but it would only return one single row.

blocks|key|1635331|text|select+*+from+table_name+where+col_Date+between+'2011/02/25'+AND+DATEADD(s,-1,DATEADD(d,1,'2011/02/27'))|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1635332|在这里，首先向当前endDate添加一天，它将是2011-02-28+00:00:00，然后减去一秒，得到结束日期2011-02-27+23:59:59。通过这样做，您可以获得给定间隔之间的所有日期。|unstyled|1635333|输出：|1635334|2011/02/25|1635335|2011/02/26|1635336|2011/02/27|1635337|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|R|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|F|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|F|7|W|8|@]|9|@]|A|$]]]|P|$]]

<pre><code>select * from table_name where col_Date between '2011/02/25' AND DATEADD(s,-1,DATEADD(d,1,'2011/02/27'))
</code></pre>

Here, first add a day to the current endDate, it will be 2011-02-28 00:00:00, then you subtract one second to make the end date 2011-02-27 23:59:59. By doing this, you can get all the dates between the given intervals.

output: 
2011/02/25 
2011/02/26 
2011/02/27

blocks|key|1420537|text|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(),'%25Y'),'-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%2B1;
+++++++++IF+a=365+THEN
++++++++++++LEAVE+one_by_one;
+++++++++END+IF;
++++++END+LOOP+one_by_one;
END+$$|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1420538|这个过程将插入从年初到现在的所有日期，只需替换“开始”和“结束”的日期，您就可以开始了！|unstyled|1420539|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>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)) &lt;&gt; 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 $$
</code></pre>

this procedure will insert all dates from the beginning of the year till now, just substitue the days of the "start" and "end", and you are ready to go!

blocks|key|1635433|text|使用MariaDB+>=+10.3和MySQL+>=+8.0中新的递归(公用表表达式)功能的优雅解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1635434|WITH+RECURSIVE+t+as+(
++++select+'2019-01-01'+as+dt
++UNION
++++SELECT+DATE_ADD(t.dt,+INTERVAL+1+DAY)+FROM+t+WHERE+DATE_ADD(t.dt,+INTERVAL+1+DAY)+<=+'2019-04-30'
)
select+*+FROM+t;|code-block|syntax|javascript|1635435|上面返回了一个介于'2019-01-01‘和'2019-04-30’之间的日期表格。|1635436|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Elegant solution using new recursive (Common Table Expressions) functionality in MariaDB >= 10.3 and MySQL >= 8.0.

<pre><code>WITH RECURSIVE t as (
 select '2019-01-01' as dt
 UNION
 SELECT DATE_ADD(t.dt, INTERVAL 1 DAY) FROM t WHERE DATE_ADD(t.dt, INTERVAL 1 DAY) &lt;= '2019-04-30'
)
select * FROM t;
</code></pre>

The above returns a table of dates between '2019-01-01' and '2019-04-30'.

blocks|key|1420599|text|你可以使用这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1420600|SELECT+CAST(cal.date_list+AS+DATE)+day_year
FROM+(
++SELECT+SUBDATE('2019-01-01',+INTERVAL+1+YEAR)+%2B+INTERVAL+xc+DAY+AS+date_list
++FROM+(
++++++++SELECT+@xi:=@xi%2B1+as+xc+from
++++++++(SELECT+1+UNION+SELECT+2+UNION+SELECT+3+UNION+SELECT+4)+xc1,
++++++++(SELECT+1+UNION+SELECT+2+UNION+SELECT+3+UNION+SELECT+4)+xc2,
++++++++(SELECT+1+UNION+SELECT+2+UNION+SELECT+3+UNION+SELECT+4)+xc3,
++++++++(SELECT+1+UNION+SELECT+2+UNION+SELECT+3+UNION+SELECT+4)+xc4,
++++++++(SELECT+1+UNION+SELECT+2+UNION+SELECT+3+UNION+SELECT+4)+xc5,
++++++++(SELECT+@xi:=-1)+xc0
++++)+xxc1
)+cal
WHERE+cal.date_list+BETWEEN+'2019-01-01'+AND+'2019-12-31'
ORDER+BY+cal.date_list+DESC;|code-block|syntax|javascript|1420601|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can use this
<pre><code>SELECT CAST(cal.date_list AS DATE) day_year
FROM (
 SELECT SUBDATE('2019-01-01', INTERVAL 1 YEAR) + INTERVAL xc DAY AS date_list
 FROM (
 SELECT @xi:=@xi+1 as xc from
 (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1,
 (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2,
 (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc3,
 (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc4,
 (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc5,
 (SELECT @xi:=-1) xc0
 ) xxc1
) cal
WHERE cal.date_list BETWEEN '2019-01-01' AND '2019-12-31'
ORDER BY cal.date_list DESC;
</code></pre>

blocks|key|1420635|text|无函数，无过程|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1420636|SET+@s+:=+'2020-01-01';
SET+@e+:=+@s+%2B+INTERVAL+1+YEAR+-+INTERVAL+1+DAY;+--+set+end+date+to+select

SELECT+CAST((DATE(@s)%2BINTERVAL+(H%2BT%2BU)+DAY)+AS+DATE)+d+--+generate+a+list+of+400+days+starting+from+@s+date+so+@e+can+not+be+more+then+@s+%2B+
FROM+(+SELECT+0+H
++++UNION+ALL+SELECT+100+UNION+ALL+SELECT+200+UNION+ALL+SELECT+300
++)+H+CROSS+JOIN+(+SELECT+0+T
++++UNION+ALL+SELECT++10+UNION+ALL+SELECT++20+UNION+ALL+SELECT++30
++++UNION+ALL+SELECT++40+UNION+ALL+SELECT++50+UNION+ALL+SELECT++60
++++UNION+ALL+SELECT++70+UNION+ALL+SELECT++80+UNION+ALL+SELECT++90
++)+T+CROSS+JOIN+(+SELECT+0+U
++++UNION+ALL+SELECT+++1+UNION+ALL+SELECT+++2+UNION+ALL+SELECT+++3
++++UNION+ALL+SELECT+++4+UNION+ALL+SELECT+++5+UNION+ALL+SELECT+++6
++++UNION+ALL+SELECT+++7+UNION+ALL+SELECT+++8+UNION+ALL+SELECT+++9
++)+U
+--+generated+lenght+of+date+list+can+be+calculated+as+fallow+1+*+H(3+%2B+1)+*+T(9+%2B+1)+*+U(9+%2B+1)+=+400
+--+it+is+crucial+to+preserve+the+numbering+convention+as+1,+2+...+20,+30...,+100,+200,+...+to+retrieve+chronological+date+selection
+--+add+more+UNION+400,+500,+600,+...+H(6+%2B+1)+if+you+want+to+select+from+more+than+700+days!
WHERE
++(DATE(@s%2BINTERVAL+(H%2BT%2BU)+DAY))+<=+DATE((@e))
ORDER+BY+d;|code-block|syntax|javascript|1420637|联合定义了可供选择的日期的长度和范围。所以一定要确保它有足够的日志。|1420638|SELECT+1+*+(3+%2B+1)+*+(9+%2B+1)+*+(9+%2B+1);+--+400+days+starting+from+from+@s|1420639|基于此article|offset|length|1420640|经过测试并按预期工作|1420641|entityMap|0|LINK|mutability|MUTABLE|url|https://jeffgarretson.blog/2012/05/04/generating-a-range-of-dates-in-mysql/^0|0|0|0|0|3|7|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|12|8|@]|9|@$M|13|N|14|1|15]]|A|$]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

<h1>No Function, No Procedures</h1>
<pre><code>SET @s := '2020-01-01';
SET @e := @s + INTERVAL 1 YEAR - INTERVAL 1 DAY; -- set end date to select

SELECT CAST((DATE(@s)+INTERVAL (H+T+U) DAY) AS DATE) d -- generate a list of 400 days starting from @s date so @e can not be more then @s + 
FROM ( SELECT 0 H
 UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
 ) H CROSS JOIN ( SELECT 0 T
 UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30
 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60
 UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90
 ) T CROSS JOIN ( SELECT 0 U
 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
 ) U
 -- generated lenght of date list can be calculated as fallow 1 * H(3 + 1) * T(9 + 1) * U(9 + 1) = 400
 -- it is crucial to preserve the numbering convention as 1, 2 ... 20, 30..., 100, 200, ... to retrieve chronological date selection
 -- add more UNION 400, 500, 600, ... H(6 + 1) if you want to select from more than 700 days!
WHERE
 (DATE(@s+INTERVAL (H+T+U) DAY)) &lt;= DATE((@e))
ORDER BY d;
</code></pre>
UNIONS define the length and range of available dates to elect from. So always make sure to make it log enough.
<pre><code>SELECT 1 * (3 + 1) * (9 + 1) * (9 + 1); -- 400 days starting from from @s
</code></pre>
Based on this <a href="https://jeffgarretson.blog/2012/05/04/generating-a-range-of-dates-in-mysql/" rel="nofollow noreferrer">article</a> 
Tested and working as expected

blocks|key|1635534|text|在@brad+answer的基础上，我使用了带有光标的CTE：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1635535|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;|code-block|syntax|javascript|1635536|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/users/472966/brad^0|2|4|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Improving on @<a href="https://stackoverflow.com/users/472966/brad">brad</a> answer, I used a CTE with a cursor:
<pre><code>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) &lt;= '2019-04-30'
 )
 SELECT DATE(dt) FROM period ORDER BY dt ) AS derived_table;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
</code></pre>

Using standard mysql functions is there a way to write a query that will return a list of days between two dates.

eg given 2009-01-01 and 2009-01-13 it would return a one column table with the values:

<pre><code> 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
</code></pre>

Edit: It appears I have not been clear. I want to GENERATE this list. I have values stored in the database (by datetime) but want them to be aggregated on a left outer join to a list of dates as above (I am expecting null from the right side of some of this join for some days and will handle this).

Get a list of dates between two dates

数据仓库

数据库

MariaDB

MySQL

使用标准的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 2...

问获取介于两个日期之间的日期列表
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取介于两个日期之间的日期列表EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取介于两个日期之间的日期列表
EN