在mysql表中查找日期范围

问题描述：

我想查找具有数据和日期列的表中的所有日期范围。如果一天中没有数据，则日期范围将被拆分。在mysql表中查找日期范围

例如如果表中有直至2015年3月3日

2015年1月1日之间的数据和有在天2015年5月1日和无数据2015年6月2日

然后我想收到的日期范围

1-1-2015 until 4-1-2015 

6-1-2015 until 5-2-2015 

7-2-2015 until 3-3-2015

有没有办法用MySQL查询做到这一点？

可以做到这两种方式（在SQL）：使用日历表（如果你有一个）或使用一个便宜的日历表模拟器与数字上的硬编码交叉连接。或者您可以在您的应用程序代码 – amdixon

中执行此操作，以便不存在直接执行该工作的查询？ – vagelis

不是内置的东西。下面的例子显示如何模拟一个日历表来做到这一点 – amdixon

答

计划

交叉联接位数一起以模拟日历表

左加入日历DataSource和识别时的日期不存在

为了在如上所述使用SQL变量RGN和边界

聚集在rgn和边界上以获得每个区域

设置

create table somedates 
(
    id integer primary key not null, 
    vdate date not null 
); 

drop view if exists digits_v; 
create view digits_v 
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 
; 

insert into somedates 
(id, vdate) 
select @id := @id + 1 as id, date_add('2015-01-01', interval a2.n * 100 + a1.n * 10 + a0.n day) as vdate 
from digits_v a2 
cross join digits_v a1 
cross join digits_v a0 
cross join (select @id := 0) params 
where date_add('2015-01-01', interval a2.n * 100 + a1.n * 10 + a0.n day) <= date('2015-03-03') 
and date_add('2015-01-01', interval a2.n * 100 + a1.n * 10 + a0.n day) not in 
(
    '2015-01-05', 
    '2015-02-06' 
) 
;

查询

select rgn, min(boundary) `from`, max(boundary) `to` 
from 
(
select active.aday, active.present, 
@rgn := if(active.present, 
      if(@boundary := active.aday, @rgn, @rgn), 
      if(@boundary := null, @rgn + 1, @rgn + 1)) as rgn, 
@boundary as boundary 
from 
(
select date_add('2015-01-01', interval a2.n * 100 + a1.n * 10 + a0.n day) as aday, 
case when sd.id is not null then true 
    else false 
end as present 
from digits_v a2 
cross join digits_v a1 
cross join digits_v a0 
left join somedates sd 
on sd.vdate = date_add('2015-01-01', interval a2.n * 100 + a1.n * 10 + a0.n day) 
where date_add('2015-01-01', interval a2.n * 100 + a1.n * 10 + a0.n day) <= date('2015-03-03') 
and date_add('2015-01-01', interval a2.n * 100 + a1.n * 10 + a0.n day) >= date('2015-01-01') 
) active 
cross join (select @rgn := 0, @boundary := date('2015-01-01')) params 
order by active.aday 
) boundaries 
group by rgn 
having `from` is not null 
;

输出

+-----+------------+------------+ 
| rgn | from |  to  | 
+-----+------------+------------+ 
| 0 | 2015-01-01 | 2015-01-04 | 
| 1 | 2015-01-06 | 2015-02-05 | 
| 2 | 2015-02-07 | 2015-03-03 | 
+-----+------------+------------+

sqlfiddle

参考

creating mysql calendar table
mysql-variable-assignment-how-to-force-assignment-order

我们其中一个人误解了这个要求。我认为这是你，但是这个问题很糟糕，这对我来说可能很容易。 – Strawberry

可能 - 怎么样？ – amdixon

让我们拭目以待。 – Strawberry

答

根据你的描述，你有一个数据集大致如下......

CREATE TABLE my_table 
(data CHAR(2) NULL 
,date DATE NOT NULL PRIMARY KEY); 

INSERT INTO my_table VALUES 
('AB','2015-01-01'), 
('AB','2015-01-02'), 
('AB','2015-01-03'), 
('AB','2015-01-04'), 
(NULL,'2015-01-05'), 
('AB','2015-01-06'), 
('...etc.''

假设是正确的，那么查询可以是这个样子......

SELECT a.date start 
    , MIN(c.date) end 
    FROM my_table a 
    LEFT 
    JOIN my_table b 
    ON b.date = a.date - INTERVAL 1 DAY 
    AND b.data IS NOT NULL 
    LEFT 
    JOIN my_table c 
    ON c.date >= a.date 
    AND c.data IS NOT NULL 
    LEFT 
    JOIN my_table d 
    ON d.date = c.date + INTERVAL 1 DAY 
    AND d.data IS NOT NULL 
WHERE b.date IS NULL 
    AND c.date IS NOT NULL 
    AND d.date IS NULL 
    AND a.data IS NOT NULL 
GROUP 
    BY a.date;

在mysql表中查找日期范围

相关推荐