当前位置： 文江博客话题详情

从日期范围生成天数

发布于 2024-08-19 04:40:28 字数 219 浏览 9 评论 0原文

我想运行像这样的查询

select ... as days where `date` is between '2010-01-20' and '2010-01-24'

并返回像这样的数据：

days
----------
2010-01-20
2010-01-21
2010-01-22
2010-01-23
2010-01-24
原文

I would like to run a query like

select ... as days where `date` is between '2010-01-20' and '2010-01-24'

And return data like:

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（30）

一袭白衣梦中忆 2024-08-26 04:40:28

此解决方案不使用循环、过程或临时表。子查询生成最近 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 秒。

如果我们扩展子查询来生成大约 0.0009 秒。 100,000 个数字（因此大约有 274 年的日期），它的运行时间为 0.0458 秒。

顺便说一句，这是一种非常可移植的技术，只需稍作调整即可适用于大多数数据库。

返回 1,000 天的 SQL Fiddle 示例

This solution uses no loops, procedures, or temp tables. The subquery generates dates for the last 10,000 days, and could be extended to go as far back or forward as you wish.

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'

Output:

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

Notes on Performance

Testing it out here, the performance is surprisingly good: the above query takes 0.0009 sec.

If we extend the subquery to generate approx. 100,000 numbers (and thus about 274 years worth of dates), it runs in 0.0458 sec.

Incidentally, this is a very portable technique that works with most databases with minor adjustments.

SQL Fiddle example returning 1,000 days

回复 原文
戏剧牡丹亭 2024-08-26 04:40:28

这是使用视图的另一种变体：

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;

Here is another variation using views:

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;

And then you can simply do (see how elegant it is?):

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

Update

It is worth noting that you will only be able to generate past dates starting from the current date. If you want to generate any kind of dates range (past, future, and in between), you will have to use this view instead:

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;
回复 原文
迷乱花海 2024-08-26 04:40:28

接受的答案不适用于 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)

Accepted answer didn't work for PostgreSQL (syntax error at or near "a").

The way you do this in PostgreSQL is by using generate_series function, i.e.:

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)
回复 原文
呆橘 2024-08-26 04:40:28

使用递归通用表表达式 (CTE)，您可以生成日期列表，然后从中进行选择。显然，您通常不想创建三百万个日期，因此这只是说明了可能性。您可以简单地限制 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)

在 Microsoft SQL Server 2005 上，生成所有可能日期的 CTE 列表需要 1 分 08 秒。生成一百年只需要不到一秒钟的时间。

Using a recursive Common Table Expression (CTE), you can generate a list of dates, then select from it. Obviously you normally wouldn't want to create three million dates, so this just illustrates the possibilities. You could simply limit the date range inside the CTE and omit the where clause from the select statement using the CTE.

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)

On Microsoft SQL Server 2005, generating the CTE list of all possible dates took 1:08. Generating one hundred years took less than a second.

回复 原文
再可℃爱ぅ一点好了 2024-08-26 04:40:28

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

MSSQL Query

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

Output

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
回复 原文
意中人 2024-08-26 04:40:28

在没有循环/游标的情况下执行此操作的老式解决方案是创建一个 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

您将用它做什么？

生成日期或数字列表以便左连接。您这样做是为了查看数据中存在间隙的位置，因为您左连接到序列数据列表 - 空值将使存在间隙的位置变得明显。

The old school solution for doing this without a loop/cursor is to create a NUMBERS table, which has a single Integer column with values starting at 1.

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

You need to populate the table with enough records to cover your needs:

INSERT INTO NUMBERS (id) VALUES (NULL);

Once you have the NUMBERS table, you can use:

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'

The absolute low-tech solution would be:

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

What would you use it for?

To generate lists of dates or numbers in order to LEFT JOIN on to. You would to this in order to see where there are gaps in the data, because you are LEFT JOINing onto a list of sequencial data - null values will make it obvious where gaps exist.

回复 原文
星軌x 2024-08-26 04:40:28

对于 Access 2010 - 需要多个步骤；我遵循与上面发布的相同的模式，但我认为我可以帮助 Access 中的某人。对我来说效果很好，我不必保留日期的种子表。

创建一个名为 DUAL 的表（类似于 Oracle DUAL 表的工作方式）

  • ID (AutoNumber)
  • DummyColumn (Text)
  • 添加一行值 (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#

For Access 2010 - multiple steps required; I followed the same pattern as posted above, but thought I could help someone in Access. Worked great for me, I didn't have to keep a seeded table of dates.

Create a table called DUAL (similar to how the Oracle DUAL table works)

  • ID (AutoNumber)
  • DummyColumn (Text)
  • Add one row values (1,"DummyRow")

Create a query named "ZeroThru9Q"; manually enter the following syntax:

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;

Create a query named "TodayMinus1KQ" (for dates before today); manually enter the following syntax:

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

Create a query named "TodayPlus1KQ" (for dates after today); manually enter the following syntax:

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;

Create a union query named "TodayPlusMinus1KQ" (for dates +/- 1000 days):

SELECT MyDate
FROM TodayMinus1KQ
UNION
SELECT MyDate
FROM TodayPlus1KQ;

Now you can use the query:

SELECT MyDate
FROM TodayPlusMinus1KQ
WHERE MyDate BETWEEN #05/01/2014# and #05/30/2014#
回复 原文
人间☆小暴躁 2024-08-26 04:40:28

在 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 毫秒。

Elegant solution using new recursive (Common Table Expressions) functionality in MariaDB >= 10.3 and 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;

The above returns a table of dates between '2019-01-01' and '2019-04-30'. It is also decently fast. Returning 1000 years worth of dates (~365,000 days) takes about 400ms on my machine.

回复 原文
人生百味 2024-08-26 04:40:28

程序+临时表：

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

Procedure + temporary table:

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
回复 原文
蘸点软妹酱 2024-08-26 04:40:28

谢谢 Pentium10 - 你让我加入了 stackoverflow :) -
这是我对 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]);

引用 MSysObjects 只是因为访问需要在 from 子句中至少计数 1 条记录的表 - 任何至少有 1 条记录的表都可以。

thx Pentium10 - you made me join stackoverflow :) -
this is my porting to msaccess - think it'll work on any version:

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]);

referenced MSysObjects just 'cause access need a table countin' at least 1 record, in a from clause - any table with at least 1 record would do.

回复 原文
萌化 2024-08-26 04:40:28

正如许多已经给出的精彩答案所述（或至少暗示），一旦您拥有一组可供使用的数字，这个问题就很容易解决。

注意：以下是 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，而且非常简单。上述查询中有 2 条关键信息：0001-01-22 的开始日期和5 的偏移。如果我们将这两条信息结合起来，那么我们显然就得到了结束日期。因此，给定两个日期，生成一个范围可以像这样分解：

  • 查找两个给定日期之间的差异（偏移量），很简单：

    <代码>-- 返回 125
    选择 ABS(DATEDIFF(d, '2014-08-22', '2014-12-25'))

    此处使用 ABS() 可确保日期顺序无关。

  • 生成一组有限的数字，也很简单：

    -- 返回数字 0-2
    选择 N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
    FROM(SELECT 'A' AS 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 中可用的全部数字。

以下函数需要大约 16 毫秒的 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
);

As stated (or at least alluded to) in many of the wonderful answers already given, this problem is easily solved once you have a set of numbers to work with.

Note: The following is T-SQL but it's simply my particular implementation of general concepts already mentioned here and on the internet at large. It should be relatively simple to convert the code to your dialect of choice.

How? Consider this query:

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

The above produces the date range 1/22/0001 - 1/27/0001 and is extremely trivial. There are 2 key pieces of information in the above query: the start date of 0001-01-22 and the offset of 5. If we combine these two pieces of information then we obviously have our end date. Thus, given two dates, generating a range can be broken down like so:

  • Find the difference between two given dates (the offset), easy:

    -- Returns 125
    SELECT ABS(DATEDIFF(d, '2014-08-22', '2014-12-25'))

    Using ABS() here ensures that the date order is irrelevant.

  • Generate a limited set of numbers, also easy:

    -- Returns the numbers 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')

    Notice we don't actually care what we're selecting FROM here. We just need a set to work with so that we count the number of rows in it. I personally use a TVF, some use a CTE, others use a numbers table instead, you get the idea. I advocate for using the most performant solution that you also understand.

Combining these two methods will solve our problem:

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));

The above example is horrible code but demonstrates how everything comes together.

More Fun

I need to do this kind of thing a lot so I encapsulated the logic into two TVFs. The first generates a range of numbers and the second uses this functionality to generate a range of dates. The math is to ensure that input order doesn't matter and because I wanted to use the full range of numbers available in GenerateRangeSmallInt.

The following function takes ~16ms of CPU time to return the maximum range of 65536 dates.

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
);
回复 原文
半步萧音过轻尘 2024-08-26 04:40:28

试试这个。

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

try this.

SELECT TO_DATE('20160210','yyyymmdd') - 1 + LEVEL AS start_day 
from DUAL
connect by level <= (TO_DATE('20160228','yyyymmdd') + 1) - TO_DATE('20160210','yyyymmdd') ;
回复 原文
初见你 2024-08-26 04:40:28

对于任何想要将此作为已保存视图的人（MySQL 不支持视图中的嵌套选择语句）：

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
...

For anyone who wants this as a saved view (MySQL doesn't support nested select statements in views):

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;

You can then do

select * from date_range

to get

date
---
2017-06-06
2017-06-05
2017-06-04
2017-06-03
2017-06-02
...
回复 原文
寄离 2024-08-26 04:40:28

您想要获取日期范围。

在您的示例中，您希望获取“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'

You'd like to get the a date range.

In your example you'd like to get the dates between '2010-01-20' and '2010-01-24'

possible solution:

 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'

Explanation

MySQL has a date_add function so

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

will give you

2010-01-21

The datediff function would let you know often you'd have to repeat this

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

which returns

 4

Getting a list of dates in a date range boils down to creating a sequence of integer numbers see generate an integer sequence in MySQL

The most upvoted answer here has taken a similar approach as https://stackoverflow.com/a/2652051/1497139 as a basis:

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

which will result in

row
1.0
2.0
3.0
4.0

The rows can now be used to create a list of dates from the given start date. To include the start date we start with row -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'
回复 原文
Saygoodbye 2024-08-26 04:40:28

一个适用于 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

A more generic answer that works in 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
回复 原文
蹲在坟头点根烟 2024-08-26 04:40:28

如果您需要超过几天的时间，您需要一张桌子。

在 mysql 中创建日期范围

然后，

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

if you will ever need more then a couple days, you need a table.

Create a date range in mysql

then,

select from days.day, count(mytable.field) as fields from days left join mytable on day=date where date between x and y;
回复 原文
黑色毁心梦 2024-08-26 04:40:28

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

如果您了解 SQL CTE 查询，那么此解决方案将帮助您解决您的问题

这里是示例

我们在一个表中有

日期: “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

说明：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

    说明：Union all 将继承上述查询的结果，直到结果变为 null，
    因此“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

  • 查询的第三部分

    从 CTE 中选择 DISTINCT DateAdd(dd,diff, StartTime) AS StartTime

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

所示

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

Generate dates between two date fields

If you are aware with SQL CTE query, then this solution will helps you to solve your question

Here is example

We have dates in one table

Table Name: “testdate”

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

Require Result:

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

Solution:

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

Explanation: CTE Recursive query explanation

  • First part of query:


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



    Explanation: firstcolumn is “startdate”, second column is difference of start and end
    date in days and it will be consider as “diff” column

  • Second part of query:


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



    Explanation: Union all will inherit result of above query until result goes null,
    So “StartTime” result is inherit from generated CTE query, and from diff, decrease - 1, so its looks like 3, 2, and 1 until 0

For example

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

Result Specification

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

  • 3rd Part of Query

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



    It will add day “diff” in “startdate” so result should be as below

Result

STARTDATE
10/24/2012
10/27/2012
10/28/2012
10/29/2012
10/30/2012
回复 原文
〃安静 2024-08-26 04:40:28

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

(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')

Shorter than accepted answer, same idea:

(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')
回复 原文
习惯成性 2024-08-26 04:40:28

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;

One more solution for mysql 8.0.1 and mariadb 10.2.2 using recursive common table expressions:

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;
回复 原文
他不在意 2024-08-26 04:40:28

即时生成这些日期是个好主意。但是，我觉得在相当大的范围内执行此操作不太舒服，因此我最终得到了以下解决方案：

  1. 创建了一个表“DatesNumbers”，它将保存用于日期计算的数字：
CREATE TABLE DatesNumbers (
    i MEDIUMINT NOT NULL,
    PRIMARY KEY (i)
)
COMMENT='Used by Dates view'
;
  1. 使用上述技术用来自的数字填充表-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
;
  1. 创建了一个视图“日期”：
SELECT
      i,
      CURRENT_DATE() + INTERVAL i DAY AS Date
FROM
    DatesNumbers

就是这样。

  • (+) 易于阅读的查询
  • (+) 没有动态数字生成
  • (+) 给出过去和未来的日期，并且没有 UNION 存在，如 这篇文章。
  • (+) “仅限过去”或“仅限未来”日期可以使用 WHERE i << 0 或 WHERE i > 0 (PK)
  • (-) '临时'表和视图被使用

It's a good idea with generating these dates on the fly. However, I do not feel myself comfortable to do this with quite large range so I've ended up with the following solution:

  1. Created a table "DatesNumbers" that will hold numbers used for dates calculation:
CREATE TABLE DatesNumbers (
    i MEDIUMINT NOT NULL,
    PRIMARY KEY (i)
)
COMMENT='Used by Dates view'
;
  1. Populated the table using above techniques with numbers from -59999 to 40000. This range will give me dates from 59999 days (~164 years) behind to 40000 days (109 years) ahead:
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
;
  1. Created a view "Dates":
SELECT
      i,
      CURRENT_DATE() + INTERVAL i DAY AS Date
FROM
    DatesNumbers

That's it.

  • (+) Easy to read queries
  • (+) No on the fly numbers generations
  • (+) Gives dates in the past and in the future and there is NO UNION in view for this as in this post.
  • (+) "In the past only" or "in the future only" dates could be filtered using WHERE i < 0 or WHERE i > 0 (PK)
  • (-) 'temporary' table & view is used
回复 原文
就像说晚安 2024-08-26 04:40:28

好吧..试试这个：
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

Alright.. Try this:
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

Use that to, say, generate a temp table, and then do a select * on the temp table. Or output the results one at a time.
What you say you want to do can't be done with a SELECT statement, but it might be doable with things specific to MySQL.
Then again, maybe you need cursors: http://dev.mysql.com/doc/refman/5.0/en/cursors.html

回复 原文
可是我不能没有你 2024-08-26 04:40:28

对于Oracle，我的解决方案是：

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

可以将Sysdate更改为特定日期，并且可以更改级别编号以提供更多日期。

For Oracle, my solution is:

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

Sysdate can be changed to specific date and level number can be changed to give more dates.

回复 原文
多彩岁月 2024-08-26 04:40:28

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

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

*在这里小提琴：http://sqlfiddle.com/ #!6/9eecb/3469

if you want the list of dates between two dates:

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

回复 原文
荒芜了季节 2024-08-26 04:40:28
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)

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)
回复 原文
红尘作伴 2024-08-26 04:40:28
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()
    );

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()
    );
回复 原文
辞慾 2024-08-26 04:40:28

RedFilters 顶级解决方案的 SQLite 版本

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

SQLite version of RedFilters top solution

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
回复 原文
不甘平庸 2024-08-26 04:40:28

改进了工作日加入自定义假期表
microsoft MSSQL 2012 for 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)

improved with weekday an joining a custom holiday table
microsoft MSSQL 2012 for powerpivot date table
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)
回复 原文
绅刃 2024-08-26 04:40:28
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'

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'
回复 原文
灯下孤影 2024-08-26 04:40:28

还可以创建一个过程来创建日历表，其中时间戳与日期不同。
如果您想要每个季度都有一个表格，

例如

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;

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

Can create a procedure also to create calendar table with timestmap different from day.
If you want a table for each quarter

e.g.

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

you can use

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

and then manipulate through

select ts, dt from calendar_table_tmp;

that give you also ts

'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'

from here you can start to add other information such as

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

or create a real table with create table statement

回复 原文
成熟的代价 2024-08-26 04:40:28

类似于 D'Arcy Rittich 的答案，但适用于 SQL SERVER

;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;

或没有 CTE

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

Similar to D'Arcy Rittich's Answer, but for SQL SERVER

;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;

or without CTE

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
回复 原文
~没有更多了~

关于作者

逆夏时光

暂无简介

0 文章
0 评论
24 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

书间行客

文章 0 评论 0

我ぃ本無心為│何有愛

文章 0 评论 0

神妖

文章 0 评论 0

undefined

文章 0 评论 0

38169838

文章 0 评论 0

彡翼

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文