我有一个包含日期范围的表格:
create table d (
date_start date,
date_end date
);
insert into d values('2014-03-05', '2014-04-10');
insert into d values('2014-05-01', '2014-06-05');
insert into d values('2014-07-10', '2014-08-15');
我想完成此表,但缺少 2014 年的日期范围。在本例中,结果为:
2014-01-01 - 2014-03-04
2014-04-11 - 2014-04-30
2014-06-06 - 2014-07-09
2014-08-16 - 2014-12-31
有mysql查询建议吗?
Edit
更好地使用这些值:
create table d (
date_start date,
date_end date
);
insert into d values('2014-06-01', '2014-06-30');
insert into d values('2014-07-01', '2014-08-03');
insert into d values('2014-09-01', '2014-09-30');
Edit 2
我就快到了:
SELECT
date_start,
date_end
FROM
(SELECT
ends.point AS date_start,
starts.point AS date_end
FROM
(SELECT
d.date_end+INTERVAL 1 DAY AS point,
@n:=@n+1 AS num
FROM
d
CROSS JOIN (SELECT @n:=1) AS init0
ORDER BY date_start
) AS ends
INNER JOIN
(SELECT
d.date_start-INTERVAL 1 DAY AS point,
@m:=@m+1 AS num
FROM
d
CROSS JOIN (SELECT @m:=0) AS init1
ORDER BY date_start
) AS starts
ON ends.num=starts.num
UNION ALL
SELECT
'2014-01-01',
MIN(date_start) - INTERVAL 1 DAY
FROM
d
WHERE
date_start>='2014-01-01'
UNION ALL
SELECT
MAX(date_end)+INTERVAL 1 DAY,
'2014-12-31'
FROM
d
WHERE
date_end <= '2014-12-31'
) as dates
WHERE
date_start < date_end
ORDER BY
date_start;
但是,此查询对于以下时间间隔集返回错误结果:
create table d (date_start date, date_end date);
insert into d values('2014-01-01', '2014-01-09');
insert into d values('2014-01-10', '2014-03-15');
insert into d values('2014-03-16', '2014-04-20');
insert into d values('2014-05-01', '2014-07-30');
insert into d values('2014-08-01', '2014-09-30');
insert into d values('2014-12-25', '2014-12-31');
它似乎无法处理像 2014-07-31 - 2014-07-31 这样的单日。