小开

好吧. . 试试这个: Http://www.devshed.com/c/a/mysql/delving-deeper-into-mysql-50/
Http://dev.mysql.com/doc/refman/5.0/en/loop-statement.html
Http://www.roseindia.net/sql/mysql-example/mysql-loop.shtml

比方说，使用它生成一个临时表，然后在临时表上执行 select * 。或者一次输出一个结果。
您所说的您想要做的事情不能通过 SELECT 语句 来完成，但是它可以通过 MySQL 特有的事情来完成。
不过，也许您需要光标: http://dev.mysql.com/doc/refman/5.0/en/cursors.html

小开

在不使用循环/游标的情况下实现这一点的老式解决方案是创建一个 NUMBERS表，该表有一个 Integer 列，值从1开始。

CREATE TABLE  `example`.`numbers` (
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

您需要在表中填充足够的记录以满足您的需求:

INSERT INTO NUMBERS (id) VALUES (NULL);

一旦你有了 NUMBERS表，你可以使用:

SELECT x.start_date + INTERVAL n.id-1 DAY
FROM NUMBERS n
JOIN (SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d') AS start_date
FROM DUAL) x
WHERE x.start_date + INTERVAL n.id-1 DAY <= '2010-01-24'

绝对低技术含量的解决方案是:

SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d')
FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-21', '%Y-%m-%d')
FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-22', '%Y-%m-%d')
FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-23', '%Y-%m-%d')
FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-24', '%Y-%m-%d')
FROM DUAL

你会用它做什么？

生成日期或数字列表，以便将 JOIN 保留到。为了查看数据中哪里存在空白，您可以这样做，因为您是左连接到一个顺序数据列表-null 值将使存在空白的地方显而易见。

小开

最佳答案

此解决方案使用 没有循环、过程或临时表。子查询生成过去10,000天的日期，并且可以根据您的需要进行扩展。

select a.Date
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
from (select 0 as a 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) as a
cross join (select 0 as a 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) as b
cross join (select 0 as a 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) as c
cross join (select 0 as a 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) as d
) a
where a.Date between '2010-01-20' and '2010-01-24'

产出:

Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20

表现须知

测试它的给你，性能是惊人的好: 以上查询需要0.0009秒。

如果我们扩展子查询以生成大约100.000个数字(因此大约相当于274年的日期) ，它的运行时间为0.0458秒。

顺便说一句，这是一种非常可移植的技术，可以在大多数数据库中使用，只需要进行一些小的调整。

返回1,000天的 SQLFiddle 示例

小开

如果你需要更多的时间，你需要一张桌子。

在 mysql 中创建一个日期范围

那么,

select from days.day, count(mytable.field) as fields from days left join mytable on day=date where date between x and y;

小开

下面是使用视图的另一种变体:

CREATE VIEW digits AS
SELECT 0 AS digit 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;


CREATE VIEW numbers AS
SELECT
ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number
FROM
digits as ones,
digits as tens,
digits as hundreds,
digits as thousands;


CREATE VIEW dates AS
SELECT
SUBDATE(CURRENT_DATE(), number) AS date
FROM
numbers;

然后你可以简单地做(看看它多么优雅?) :

SELECT
date
FROM
dates
WHERE
date BETWEEN '2010-01-20' AND '2010-01-24'
ORDER BY
date

更新

值得注意的是，您将只能生成 从当前日期开始的过去日期。如果要生成任何类型的日期范围(过去、未来和中间) ，则必须使用以下视图:

CREATE VIEW dates AS
SELECT
SUBDATE(CURRENT_DATE(), number) AS date
FROM
numbers
UNION ALL
SELECT
ADDDATE(CURRENT_DATE(), number + 1) AS date
FROM
numbers;

小开

在两个日期字段之间生成日期

如果您了解 SQLCTE 查询，那么这个解决方案将帮助您解决您的问题

下面是示例

我们在一张桌子上有约会

表名称: “ testdate”

STARTDATE   ENDDATE
10/24/2012  10/24/2012
10/27/2012  10/29/2012
10/30/2012  10/30/2012

要求成绩:

STARTDATE
10/24/2012
10/27/2012
10/28/2012
10/29/2012
10/30/2012

解决方案:

WITH CTE AS
(SELECT DISTINCT convert(varchar(10),StartTime, 101) AS StartTime,
datediff(dd,StartTime, endTime) AS diff
FROM dbo.testdate
UNION ALL SELECT StartTime,
diff - 1 AS diff
FROM CTE
WHERE diff<> 0)
SELECT DISTINCT DateAdd(dd,diff, StartTime) AS StartTime
FROM CTE

递归查询解释

问题的第一部分:

SELECT DISTINCT convert(varchar(10), StartTime, 101) AS StartTime, datediff(dd, StartTime, endTime) AS diff FROM dbo.testdate

说明: 第一列是“起始日期”，第二列是开始和结束的不同日期，则视为「差异」栏
问题的第二部分:

UNION ALL SELECT StartTime, diff-1 AS diff FROM CTE WHERE diff<>0

说明: 联合所有将继承上述查询的结果，直到结果为空, 因此“ StartTime”结果是从生成的 CTE 查询继承的，并且从 diff 减少 -1，所以它看起来像3、2和1直到0

比如说

STARTDATE   DIFF
10/24/2012  0
10/27/2012  0
10/27/2012  1
10/27/2012  2
10/30/2012  0

结果规格

STARTDATE       Specification
10/24/2012  --> From Record 1
10/27/2012  --> From Record 2
10/27/2012  --> From Record 2
10/27/2012  --> From Record 2
10/30/2012  --> From Record 3

质询的第三部分

SELECT DISTINCT DateAdd(dd,diff, StartTime) AS StartTime FROM CTE

它将在“ startdate”中添加 day“ diff”，因此结果应该如下所示

结果

STARTDATE
10/24/2012
10/27/2012
10/28/2012
10/29/2012
10/30/2012

小开

使用递归公共表表达式(CTE) ，可以生成一个日期列表，然后从中进行选择。显然，你通常不会想要创建300万个日期，所以这只是说明了可能性。您可以简单地限制 CTE 中的日期范围，并使用 CTE 从 select 语句中省略 where 子句。

with [dates] as (
select convert(datetime, '1753-01-01') as [date] --start
union all
select dateadd(day, 1, [date])
from [dates]
where [date] < '9999-12-31' --end
)
select [date]
from [dates]
where [date] between '2013-01-01' and '2013-12-31'
option (maxrecursion 0)

在2005年 Microsoft SQL Server，生成所有可能日期的 CTE 清单需要1:08。产生一百年不到一秒钟。

小开

对于甲骨文，我的解决方案是:

select trunc(sysdate-dayincrement, 'DD')
from dual, (select level as dayincrement
from dual connect by level <= 30)

Sysdate 可以更改为特定日期，级别号可以更改为更多日期。

小开

对于 Access 2010 -需要多个步骤; 我遵循上面提到的相同模式，但认为我可以帮助 Access 中的某人。对我来说很有用，我不需要保留一个种子表的日期。

创建一个名为 DUAL 的表(类似于 OracleDUAL 表的工作方式)

ID (自动编号)
DummyColumn (文本)
添加一行值(1，“ DummyRow”)

创建一个名为“ ZeroThru9Q”的查询; 手动输入以下语法:

SELECT 0 AS a
FROM dual
UNION ALL
SELECT 1
FROM dual
UNION ALL
SELECT 2
FROM dual
UNION ALL
SELECT 3
FROM dual
UNION ALL
SELECT 4
FROM dual
UNION ALL
SELECT 5
FROM dual
UNION ALL
SELECT 6
FROM dual
UNION ALL
SELECT 7
FROM dual
UNION ALL
SELECT 8
FROM dual
UNION ALL
SELECT 9
FROM dual;

创建一个名为“ TodayMinus1KQ”的查询(用于今天之前的日期) ; 手动输入以下语法:

SELECT date() - (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM
(SELECT *
FROM ZeroThru9Q) AS a,


(SELECT *
FROM ZeroThru9Q) AS b,


(SELECT *
FROM ZeroThru9Q) AS c

创建一个名为“ TodayPlus1KQ”的查询(用于今天之后的日期) ; 手动输入以下语法:

SELECT date() + (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM
(SELECT *
FROM ZeroThru9Q) AS a,


(SELECT *
FROM ZeroThru9Q) AS b,


(SELECT *
FROM ZeroThru9Q) AS c;

创建一个名为“ TodayPlusMinus1KQ”(表示日期 +/-1000天)的联合查询:

SELECT MyDate
FROM TodayMinus1KQ
UNION
SELECT MyDate
FROM TodayPlus1KQ;

现在您可以使用查询:

SELECT MyDate
FROM TodayPlusMinus1KQ
WHERE MyDate BETWEEN #05/01/2014# and #05/30/2014#

小开

程序 + 临时表:

DELIMITER $$


CREATE DEFINER=`root`@`localhost` PROCEDURE `days`(IN dateStart DATE, IN dateEnd DATE)
BEGIN


CREATE TEMPORARY TABLE IF NOT EXISTS date_range (day DATE);


WHILE dateStart <= dateEnd DO
INSERT INTO date_range VALUES (dateStart);
SET dateStart = DATE_ADD(dateStart, INTERVAL 1 DAY);
END WHILE;


SELECT * FROM date_range;
DROP TEMPORARY TABLE IF EXISTS date_range;


END

小开

XPentium10-你让我加入堆栈溢出:)- 这是我对 msaccess 的移植——想想它能在任何版本上工作:

SELECT date_value
FROM (SELECT a.espr1+(10*b.espr1)+(100*c.espr1) AS integer_value,
dateadd("d",integer_value,dateserial([start_year], [start_month], [start_day])) as date_value
FROM (select * from
(
select top 1 "0" as espr1 from MSysObjects
union all
select top 1 "1" as espr2 from MSysObjects
union all
select top 1 "2" as espr3 from MSysObjects
union all
select top 1 "3" as espr4 from MSysObjects
union all
select top 1 "4" as espr5 from MSysObjects
union all
select top 1 "5" as espr6 from MSysObjects
union all
select top 1 "6" as espr7 from MSysObjects
union all
select top 1 "7" as espr8 from MSysObjects
union all
select top 1 "8" as espr9 from MSysObjects
union all
select top 1 "9" as espr9 from MSysObjects
) as a,
(
select top 1 "0" as espr1 from MSysObjects
union all
select top 1 "1" as espr2 from MSysObjects
union all
select top 1 "2" as espr3 from MSysObjects
union all
select top 1 "3" as espr4 from MSysObjects
union all
select top 1 "4" as espr5 from MSysObjects
union all
select top 1 "5" as espr6 from MSysObjects
union all
select top 1 "6" as espr7 from MSysObjects
union all
select top 1 "7" as espr8 from MSysObjects
union all
select top 1 "8" as espr9 from MSysObjects
union all
select top 1 "9" as espr9 from MSysObjects
) as b,
(
select top 1 "0" as espr1 from MSysObjects
union all
select top 1 "1" as espr2 from MSysObjects
union all
select top 1 "2" as espr3 from MSysObjects
union all
select top 1 "3" as espr4 from MSysObjects
union all
select top 1 "4" as espr5 from MSysObjects
union all
select top 1 "5" as espr6 from MSysObjects
union all
select top 1 "6" as espr7 from MSysObjects
union all
select top 1 "7" as espr8 from MSysObjects
union all
select top 1 "8" as espr9 from MSysObjects
union all
select top 1 "9" as espr9 from MSysObjects
) as c
)  as d)
WHERE date_value
between dateserial([start_year], [start_month], [start_day])
and dateserial([end_year], [end_month], [end_day]);

只是因为访问需要一个至少包含1条记录的表，在 from 子句中-任何至少包含1条记录的表都可以。

小开

正如已经给出的许多精彩答案中所陈述的(或者至少是间接提到的) ，一旦你有了一组数字可以处理，这个问题就很容易解决了。

注意: 以下是 T-SQL，但它只是我在这里和整个互联网上已经提到的一般概念的特殊实现。将代码转换为您选择的方言应该相对简单。

怎么做? 考虑一下这个问题:

SELECT DATEADD(d, N, '0001-01-22') FROM Numbers -- A table containing the numbers 0 through N WHERE N <= 5;

上面生成的日期范围是1/22/0001-1/27/0001，非常简单。在上面的查询中有两个关键信息: 0001-01-22的 开始日期和 5的偏移。如果我们把这两条信息结合起来，显然我们就有了结束的日期。因此，给定两个日期，生成一个范围可以这样分解:

找出两个给定日期之间的差异(偏移量) ，很简单:

——返回125 选择 ABS (DATEDIFF (d,’2014-08-22’,’2014-12-25’))

在这里使用 ABS()可以确保日期顺序是不相关的。

生成一组有限的数字，也很容易:

——返回数字0-2 SELECT N = ROW _ NUMBER () OVER (ORDER BY (SELECT NULL))-1 FROM (SELECT‘ A’AS S UNION ALL SELECT‘ A’UNION ALL SELECT‘ A’)

注意，我们实际上并不关心在这里选择的是什么 FROM。我们只需要一个工作集，以便我们计算其中的行数。我个人使用 TVF，有些使用 CTE，有些使用数字表来代替，你明白了吧。我主张使用您也能理解的最高性能解决方案。

将这两种方法结合起来将解决我们的问题:

DECLARE @date1 DATE = '9001-11-21'; DECLARE @date2 DATE = '9001-11-23'; SELECT D = DATEADD(d, N, @date1) FROM ( SELECT N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 FROM (SELECT 'A' AS S UNION ALL SELECT 'A' UNION ALL SELECT 'A') S ) Numbers WHERE N <= ABS(DATEDIFF(d, @date1, @date2));

上面的例子是可怕的代码，但是演示了如何将所有代码组合在一起。

更多乐趣

我需要做很多这样的事情，所以我把逻辑封装成两个 TVF。第一个函数生成一个数字范围，第二个函数使用此功能生成一个日期范围。数学是为了确保输入顺序无关紧要，因为我想使用 GenerateRangeSmallInt中可用的所有数字。

下面的函数需要大约16ms 的 CPU 时间才能返回65536个日期的最大范围。

CREATE FUNCTION dbo.GenerateRangeDate ( @date1 DATE, @date2 DATE ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT D = DATEADD(d, N + 32768, CASE WHEN @date1 <= @date2 THEN @date1 ELSE @date2 END) FROM dbo.GenerateRangeSmallInt(-32768, ABS(DATEDIFF(d, @date1, @date2)) - 32768) ); GO CREATE FUNCTION dbo.GenerateRangeSmallInt ( @num1 SMALLINT = -32768 , @num2 SMALLINT = 32767 ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( WITH Numbers(N) AS ( SELECT N FROM(VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 16 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 32 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 48 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 64 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 80 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 96 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 112 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 128 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 144 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 160 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 176 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 192 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 208 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 224 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 240 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 256 ) V (N) ) SELECT TOP(ABS(CAST(@num1 AS INT) - CAST(@num2 AS INT)) + 1) N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + CASE WHEN @num1 <= @num2 THEN @num1 ELSE @num2 END - 1 FROM Numbers A , Numbers B );

小开

MSSQL 查询

select datetable.Date from ( select DATEADD(day,-(a.a + (10 * b.a) + (100 * c.a)),getdate()) AS Date from (select 0 as a 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) as a cross join (select 0 as a 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) as b cross join (select 0 as a 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) as c ) datetable where datetable.Date between '2014-01-20' and '2014-01-24' order by datetable.Date DESC

输出

Date ----- 2014-01-23 12:35:25.250 2014-01-22 12:35:25.250 2014-01-21 12:35:25.250 2014-01-20 12:35:25.250

小开

如果您想要两个日期之间的日期列表:

create table #dates ([date] smalldatetime) while @since < @to begin insert into #dates(dateadd(day,1,@since)) set @since = dateadd(day,1,@since) end select [date] from #dates

* Fiddle here: http://sqlfiddle.com/#!6/9eecb/3469

小开

接受的答案不适用于 PostgreSQL (“ a”处或附近的语法错误)。

在 PostgreSQL 中这样做的方法是使用 generate_series函数，即:

SELECT day::date FROM generate_series('2010-01-20', '2010-01-24', INTERVAL '1 day') day; day ------------ 2010-01-20 2010-01-21 2010-01-22 2010-01-23 2010-01-24 (5 rows)

小开

set language 'SPANISH' DECLARE @table table(fechaDesde datetime , fechaHasta datetime ) INSERT @table VALUES('20151231' , '20161231'); WITH x AS ( SELECT DATEADD( m , 1 ,fechaDesde ) as fecha FROM @table UNION ALL SELECT DATEADD( m , 1 ,fecha ) FROM @table t INNER JOIN x ON DATEADD( m , 1 ,x.fecha ) <= t.fechaHasta ) SELECT LEFT( CONVERT( VARCHAR, fecha , 112 ) , 6 ) as Periodo_Id ,DATEPART ( dd, DATEADD(dd,-(DAY(fecha)-1),fecha)) Num_Dia_Inicio ,DATEADD(dd,-(DAY(fecha)-1),fecha) Fecha_Inicio ,DATEPART ( mm , fecha ) Mes_Id ,DATEPART ( yy , fecha ) Anio ,DATEPART ( dd, DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha))) Num_Dia_Fin ,DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha)) ultimoDia ,datename(MONTH, fecha) mes ,'Q' + convert(varchar(10), DATEPART(QUARTER, fecha)) Trimestre_Name FROM x OPTION(MAXRECURSION 0)

小开

比接受的答案短，同样的想法:

(SELECT TRIM('2016-01-05' + INTERVAL a + b DAY) date FROM (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) d, (SELECT 0 b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40) m WHERE '2016-01-05' + INTERVAL a + b DAY <= '2016-01-21')

小开

DELIMITER $$ CREATE PROCEDURE GenerateRangeDates(IN dateStart DATE, IN dateEnd DATE) BEGIN CREATE TEMPORARY TABLE IF NOT EXISTS dates (day DATE); loopDate: LOOP INSERT INTO dates(day) VALUES (dateStart); SET dateStart = DATE_ADD(dateStart, INTERVAL 1 DAY); IF dateStart <= dateEnd THEN ITERATE loopDate; ELSE LEAVE loopDate; END IF; END LOOP loopDate; SELECT day FROM dates; DROP TEMPORARY TABLE IF EXISTS dates; END $$ -- Call procedure call GenerateRangeDates( now() - INTERVAL 40 DAY, now() );

小开

SQLite 版本的 RedFilters top 解决方案

select d.Date from ( select date(julianday('2010-01-20') + (a.a + (10 * b.a) + (100 * c.a))) as Date from (select 0 as a 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) as a cross join (select 0 as a 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) as b cross join (select 0 as a 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) as c ) d where d.Date between '2010-01-20' and '2010-01-24' order by d.Date

小开

改进与工作日一个 加入自定义假日表 MicrosoftMSSQL2012 Powerpivot 日期表 Https://gist.github.com/josy1024/cb1487d66d9e0ccbd420bc4a23b6e90e

with [dates] as ( select convert(datetime, '2016-01-01') as [date] --start union all select dateadd(day, 1, [date]) from [dates] where [date] < '2018-01-01' --end ) select [date] , DATEPART (dw,[date]) as Wochentag , (select holidayname from holidaytable where holidaytable.hdate = [date]) as Feiertag from [dates] where [date] between '2016-01-01' and '2016-31-12' option (maxrecursion 0)

小开

试试这个。

SELECT TO_DATE('20160210','yyyymmdd') - 1 + LEVEL AS start_day from DUAL connect by level <= (TO_DATE('20160228','yyyymmdd') + 1) - TO_DATE('20160210','yyyymmdd') ;

小开

对于任何希望将其作为保存视图的人(MySQL 不支持视图中嵌套的 select 语句) :

create view zero_to_nine as select 0 as n 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; create view date_range as select curdate() - INTERVAL (a.n + (10 * b.n) + (100 * c.n)) DAY as date from zero_to_nine as a cross join zero_to_nine as b cross join zero_to_nine as c;

然后你可以做

select * from date_range

得到

date --- 2017-06-06 2017-06-05 2017-06-04 2017-06-03 2017-06-02 ...

小开

随时生成这些日期是个好主意。然而，我觉得自己不适合在这么大的范围内做这件事，所以我最终得出了以下解决方案:

创建一个表“ DatesNumbers”，其中包含用于计算日期的数字:

CREATE TABLE DatesNumbers ( i MEDIUMINT NOT NULL, PRIMARY KEY (i) ) COMMENT='Used by Dates view' ;

使用上述技术填充数字从 -59999到40000的表。这个范围将给我从59999天(约164年)到40000天(109年)之前的日期:

INSERT INTO DatesNumbers SELECT a.i + (10 * b.i) + (100 * c.i) + (1000 * d.i) + (10000 * e.i) - 59999 AS i FROM (SELECT 0 AS i 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) AS a, (SELECT 0 AS i 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) AS b, (SELECT 0 AS i 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) AS c, (SELECT 0 AS i 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) AS d, (SELECT 0 AS i 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) AS e ;

创建了一个视图“日期”:

SELECT i, CURRENT_DATE() + INTERVAL i DAY AS Date FROM DatesNumbers

就是这样。

(+)易于阅读的查询

(+)没有在苍蝇数代

(+)给出过去和将来的日期，在这篇文章中没有 UNION。

(+)使用 WHERE i < 0或 WHERE i > 0(PK)可以过滤“仅在过去”或“仅在将来”的日期

(-)使用“临时”表格和视图

小开

使用递归公共表表达式的 mysql 8.0.1和 mariadb 10.2.2的另一个解决方案:

with recursive dates as ( select '2010-01-20' as date union all select date + interval 1 day from dates where date < '2010-01-24' ) select * from dates;

小开

你想要一个日期范围。

在您的示例中，您希望获得“2010-01-20”和“2010-01-24”之间的日期

可能的解决办法:

select date_add('2010-01-20', interval row day) from ( SELECT @row := @row + 1 as row FROM (select 0 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 6 union all select 7 union all select 8 union all select 9) t, (select 0 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 6 union all select 7 union all select 8 union all select 9) t2, (select 0 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 6 union all select 7 union all select 8 union all select 9) t3, (select 0 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 6 union all select 7 union all select 8 union all select 9) t4, (SELECT @row:=-1) r ) sequence where date_add('2010-01-20', interval row day) <= '2010-01-24'

解释

MySQL 有一个 Date _ add函数，所以

select date_add('2010-01-20', interval 1 day)

会给你

2010-01-21

Datediff函数会让你知道你必须经常重复这个步骤

select datediff('2010-01-24', '2010-01-20')

回来了

4

获取日期范围内的日期列表归结为创建一个整数序列参见在 mySQL 中生成一个整数数列

这里最受欢迎的答案采用了与 https://stackoverflow.com/a/2652051/1497139类似的方法作为基础:

SELECT @row := @row + 1 as row FROM (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, (SELECT @row:=0) r limit 4

这将导致

row 1.0 2.0 3.0 4.0

现在可以使用这些行从给定的开始日期创建一个日期列表。为了包含开始日期，我们从行 -1开始;

select date_add('2010-01-20', interval row day) from ( SELECT @row := @row + 1 as row FROM (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, (SELECT @row:=-1) r ) sequence where date_add('2010-01-20', interval row day) <= '2010-01-24'

小开

WITH Digits AS (SELECT 0 D 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), Dates AS (SELECT adddate('1970-01-01',t4.d*10000 + t3.d*1000 + t2.d*100 + t1.d*10 +t0.d) AS date FROM Digits AS t0, Digits AS t1, Digits AS t2, Digits AS t3, Digits AS t4) SELECT * FROM Dates WHERE date BETWEEN '2017-01-01' AND '2017-12-31'

小开

在 MariaDB > = 10.3和 MySQL > = 8.0中使用新的递归(公共表表达式)功能的优雅解决方案。

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;

以上返回“2019-01-01”和“2019-04-30”之间的日期表。它的速度也相当快。在我的机器上，返回1000年的日期(约365,000天)大约需要400毫秒。

小开

也可以创建一个过程来创建与 timestmap < strong > 不同于 day 的 日历表。 如果你想要一个表，每季度

例如:。

2019-01-22 08:45:00
2019-01-22 09:00:00
2019-01-22 09:15:00
2019-01-22 09:30:00
2019-01-22 09:45:00
2019-01-22 10:00:00

你可以利用

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_calendar_table`()
BEGIN


select unix_timestamp('2014-01-01 00:00:00') into @startts;
select unix_timestamp('2025-01-01 00:00:00') into @endts;


if ( @startts < @endts ) then


DROP TEMPORARY TABLE IF EXISTS calendar_table_tmp;


CREATE TEMPORARY TABLE calendar_table_tmp (ts int, dt datetime);


WHILE ( @startts < @endts)
DO
SET @startts = @startts + 900;
INSERT calendar_table_tmp VALUES (@startts, from_unixtime(@startts));
END WHILE;


END if;


END

然后通过

select ts, dt from calendar_table_tmp;

这也给了你这个

'1548143100', '2019-01-22 08:45:00'
'1548144000', '2019-01-22 09:00:00'
'1548144900', '2019-01-22 09:15:00'
'1548145800', '2019-01-22 09:30:00'
'1548146700', '2019-01-22 09:45:00'
'1548147600', '2019-01-22 10:00:00'

从这里您可以开始添加其他信息，如

select ts, dt, weekday(dt) as wd from calendar_table_tmp;

或者使用 创建表语句创建一个真实的表

小开

一个在 AWS MySQL 中工作的更通用的答案。

select datetable.Date
from (
select date_format(adddate(now(),-(a.a + (10 * b.a) + (100 * c.a))),'%Y-%m-%d') AS Date
from (select 0 as a 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) as a


cross join (select 0 as a 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) as b


cross join (select 0 as a 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) as c
) datetable
where datetable.Date between now() - INTERVAL 14 Day and Now()
order by datetable.Date DESC

小开

类似于 D’Arcy Ritchich的答案，但是用于 SQLSERVER

;WITH t AS
(
SELECT n = a.n * 10 + b.n * 100 + c.n
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) a(n)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) b(n)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) c(n)
)
SELECT DATEADD(DAY, t.n, '2022-01-01')
FROM t
ORDER BY T.n;

或者没有慢性创伤性脑病

SELECT DATEADD(DAY, nums.n, '2022-01-01') AS d FROM (
SELECT n = a.n * 10 + b.n * 100 + c.n
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) a(n)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) b(n)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) c(n)
) nums
order by d

从日期范围产生天数

你会用它做什么？

在两个日期字段之间生成日期

如果您了解 SQLCTE 查询，那么这个解决方案将帮助您解决您的问题