MySQL SELECT用于迭代日期 [英] MySQL SELECT for iterating through dates

问题描述

我有这个MySql表（简化）：

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

我需要一个SELECT来检查例如之间的每个日期2015-10-01和2015-12-31并返回3（或任意数字）ID重叠的日期。有些日期没有任何记录，而其他日期可能有很多。

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.

在上表中，2015-10-06和2015-10-07是共享的 通过3条记录，这意味着那些是我需要返回的日期：

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

我当然可以让我的PHP脚本迭代指定范围内的每个日期并计算每个日期的记录，但我猜如果可以在MySql中完成整个操作会更快吗？

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?

推荐答案

计划

• 创建一个带有一些十进制逻辑的日历数据源（所有数字表示为* 10 ^ n + ... a0 * 10 ^ 0） digits_v 和date_add约
  数字


• 将日历数据加入 tbl_cards 条件属于间隔


• 汇总超过h aving count等于3


• 使用变量创建索引字段输出

• 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

---

设置

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
;

查询

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
;

输出

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

sqlfiddle

sqlfiddle

参考

• 创建mysql日历表

• creating mysql calendar table

这篇关于MySQL SELECT用于迭代日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！