MySQL SELECT for iterating through dates

2019-07-09 01:09发布

I have this MySql table (simplified):

tbl_cards
ID  FROM        TO
--------------------------
 1  2015-10-01  2015-10-08
 2  2015-10-06  2015-10-12
 3  2015-10-06  2015-10-15
 4  ...

I need a SELECT which checks every date between e.g. 2015-10-01 and 2015-12-31 and returns the dates where 3 (or an arbitrary number) ID's overlap. Some dates won't have any records, while others may have a lot.

In the table above, 2015-10-06 and 2015-10-07 are "shared" by 3 records, which means that those are the dates I need returned:

Index  Date
-----------------
 0     2015-10-06
 1     2015-10-07
 2     2015-10-08

I can of course make my php script iterate every date in the specified span and count the records for each date, but I'm guessing it would be faster if the whole operation can be done inside MySql?

1条回答
你好瞎i
2楼-- · 2019-07-09 01:36

plan

  • create a calendar data source with some decimal logic ( all numbers expressed as an*10^n + ... a0*10^0 ) with digits_v and date_add around numbers
  • join calender data to tbl_cards with condition falls in interval
  • aggregate over above having count equal to 3
  • use a variable to create index field output

setup

create table tbl_cards
(
  id integer primary key not null,
  `from` date not null,
  `to` date not null
);

insert into tbl_cards
( id, `from`, `to` )
values
( 1,  '2015-10-01',  '2015-10-08' ),
( 2,  '2015-10-06',  '2015-10-12' ),
( 3,  '2015-10-06',  '2015-10-15' )
;

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
;

query

select @index := @index + 1 as `Index`, `Date`
from
(
  select date_format(calendar.dy, '%Y-%m-%d') as `Date`
  from
  (
    select date_add(date('2015-10-01'), interval a2.n * 100 + a1.n * 10 + a0.n day) as dy
    from digits_v a2
    cross join digits_v a1
    cross join digits_v a0
    where date_add('2015-10-01', interval a2.n * 100 + a1.n * 10 + a0.n day) 
    <=    date('2015-12-31')
    order by date_add('2015-10-01', interval a2.n * 100 + a1.n * 10 + a0.n day)
  ) calendar
  inner join tbl_cards t
  on calendar.dy between t.`from` and t.`to`
  group by calendar.dy
  having count(calendar.dy) = 3
) dts
cross join ( select @index := -1 ) params
;

output

+-------+------------+
| Index |    Date    |
+-------+------------+
|     0 | 2015-10-06 |
|     1 | 2015-10-07 |
|     2 | 2015-10-08 |
+-------+------------+

sqlfiddle


reference

查看更多
登录 后发表回答