如何在MySQL中显示介于到给定值之间的日期和另一列

Question

我有一个MySQL表(A)，我希望最终结果看起来像(B)。有人知道我怎样才能完成这项任务吗？我猜是循环/存储过程，但我不确定，而且我对循环和存储过程还很陌生。

(A)
Name    str_date        end_date
Lorenzo 2010-10-09  2010-10-11
Karen   2010-09-10  2010-09-10
Mike    2010-06-01  2010-06-03

(B)
Name     Date
Lorenzo  2010-10-09
Lorenzo  2010-10-10
Lorenzo  2010-10-11
Karen    2010-09-10
Mike     2010-06-01
Mike     2010-06-02
Mike     2010-06-03

Answer 1

甚至不要试图在mysql中这样做，使用您的服务器端编程语言来格式化数据。

如果你真的，真的，真的想:创建一个包含所有需要的日期的表(即从1970-01-01到2030-01-01)，然后做一些类似的事情(未测试)：

 -- ddl 
 CREATE TABLE all_dates ( date DATE, PRIMARY KEY (date) );

 -- query
 SELECT ad.date, A.Name
 FROM all_dates ad
 JOIN A ON A.str_date <= ad.date AND A.end_date >= ad.date

但仍然是它的服务器端任务，而不是db。

Answer 2

尝试如下所示：

/* create view that will help us to to create calendar table */
create or replace view n as select 0 as n union all select 1 union 
    select 2 union select 3 union 
    select 4 union select 5;
drop temporary table if exists cal;
/* create calendar table */
create temporary table cal (d datetime primary key);
set @i = 0;
/* select min date value */
select @d := min(str_date) from tab;
/* create table with dates based on min date*/
insert into cal
select date_add(@d, interval @i := @i + 1 day) from n a, n b, n c, n d, n e;

/* join date to calendar table */
select Name, c.d as Date
from tab t
join cal c on t.str_date <= c.d and t.end_date >= c.d
order by c.d desc;

别忘了清理一下：

drop view if exists n;
drop table if exists cal;

结果：

Name        Date
Lorenzo     2010-10-11 00:00:00
Lorenzo     2010-10-10 00:00:00
Lorenzo     2010-10-09 00:00:00
Karen       2010-09-10 00:00:00
Mike        2010-06-03 00:00:00
Mike        2010-06-02 00:00:00

增加了更简单的版本：

/* create view that allows generate integers */
create or replace view n as select 0 as n union all select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9;

/* use generated integers to generate dates and join them */
select Name, c.d as Date
from tab t
join (
    select date_add(t.d, interval i day) as d
    from (select min(str_date) as d from tab) t
    join (select a.n + b.n * 10 + c.n * 100 + d.n * 1000 as i from n a, n b, n c, n d) n
) c on t.str_date <= c.d and t.end_date >= c.d
order by c.d desc;