我想要运行一个查询,例如
select ... as days where `date` is between '2010-01-20' and '2010-01-24'
并返回像这样的数据:
天数 ---------- 2010年01月20日 2010年01月21日 2010年01月22日 2010年01月23日 2010年01月24日
对于任何想将此保存为视图的人(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
...
试试这个。
SELECT TO_DATE('20160210','yyyymmdd') - 1 + LEVEL AS start_day
from DUAL
connect by level <= (TO_DATE('20160228','yyyymmdd') + 1) - TO_DATE('20160210','yyyymmdd') ;
正如许多出色的答案已经提到(或者至少暗示),一旦你有了一组要处理的数字,这个问题就很容易解决。
注意: 以下是T-SQL代码,但它只是我对这里和互联网上广泛提到的一般概念的特定实现。将代码转换为您选择的方言应该相对简单。
怎么做?考虑以下查询:
SELECT DATEADD(d, N, '0001-01-22')
FROM Numbers -- A table containing the numbers 0 through N
WHERE N <= 5;
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
);
您希望获取一个日期范围。
在您的示例中,您希望获取 '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://dev59.com/1HVC5IYBdhLWcg3wZwTj#2652051类似的方法作为基础:
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'
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
以下是示例
我们有一个表中的日期
表名:“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
解释:第一列是“开始日期”,第二列是开始和结束日期之间的天数差异,并将其视为“diff”列。
查询的第二部分:
UNION ALL SELECT StartTime, diff-1 AS diff FROM CTE WHERE diff<>0
解释:Union all将继承上述查询的结果,直到结果为空。因此,“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”中添加“diff”天,因此结果应如下所示
结果
STARTDATE
10/24/2012
10/27/2012
10/28/2012
10/29/2012
10/30/2012
比接受的答案更短,但思路相同:
(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')
如果你需要超过几天的时间,你需要一个表格。
然后,
select from days.day, count(mytable.field) as fields from days left join mytable on day=date where date between x and y;
动态生成这些日期是个好主意。但是,如果涉及到很大的范围,我会感到不太自在,所以我最终采用了以下解决方案:
CREATE TABLE DatesNumbers (
i MEDIUMINT NOT NULL,
PRIMARY KEY (i)
)
COMMENT='Used by Dates view'
;
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
就是这样。
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;
insert into table select ... as days date between '' and ''
- Pentium10