php - 用于遍历日期的 MySQL SELECT

Question

我有这个 MySql 表(简化):

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 重叠的日期。有些日期没有任何记录，而其他日期可能有很多。

在上表中，2015-10-06 和 2015-10-07 被 3 条记录“共享”，这意味着这些是我需要返回的日期:

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

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

Answer 1

计划

• 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

---

引用

• creating mysql calendar table