Query to select intervals from a table
I have a table that defines time intervals.
___________________开发者_Python百科____________
| id | description | start_date |
|____|_____________|____________|
| 1 | First | NULL |
| 3 | Third | 2009-12-18 |
| 5 | Second | 2009-10-02 |
| 4 | Last | 2009-12-31 |
|____|_____________|____________|
It stores only the start date, the end date is a day before next date that follows.
I would like to have the next result:
____________________________________________
| id | description | start_date | end_date |
|____|_____________|____________|____________|
| 1 | First | NULL | 2009-10-01 |
| 5 | Second | 2009-10-02 | 2009-12-17 |
| 3 | Third | 2009-12-18 | 2009-12-30 |
| 4 | Last | 2009-12-31 | NULL |
|____|_____________|____________|____________|
How should I write this query, since a row contains values from other rows?
(I think MySQL function DATE_SUB
could be useful.)
SELECT d.id, d.description, MIN(d.start_date), MIN(d2.start_date) - INTERVAL 1
DAY AS end_date
FROM start_dates d
LEFT OUTER JOIN start_dates d2 ON ( d2.start_date > d.start_date OR d.start_date IS NULL )
GROUP BY d.id, d.description
ORDER BY d.start_date ASC
try
select id, description, start_date, end_date from
(
select @rownum_start:=@rownum_start+1 rank, id, description, start_date
from inter, (select @rownum_start:=0) p
order by start_date
) start_dates
left join
(
select @rownum_end:=@rownum_end+1 rank, start_date - interval 1 day as end_date
from inter, (select @rownum_end:=0) p
where start_date is not null
order by start_date
) end_dates
using (rank)
where inter
is your table
This actually returns:
mysql> select id, description, start_date, end_date from ...
+----+-------------+------------+------------+
| id | description | start_date | end_date |
+----+-------------+------------+------------+
| 1 | First | NULL | 2009-10-01 |
| 5 | Second | 2009-10-02 | 2009-12-17 |
| 3 | Third | 2009-12-18 | 2009-12-30 |
| 4 | Last | 2009-12-31 | NULL |
+----+-------------+------------+------------+
4 rows in set (0.00 sec)
if you are using PHP, just calculate the previous date in the script. that's easier.
if not, would something like:
SELECT ID,description,start_date,start_date-1 AS end_date FROM ...
work?
UPDATE: it works partially, as it returns 20091224 for startdate 2009-12-25 but won't work for dates like 2009-12-01 etc.
try
Select d.Id, d.Description, d.Start_Date,
n.Start_Date - 1 EndDate
From Table d
Left Join Table n
On n.Start_Date =
(Select Min(Start_date)
From Table
Where Start_Date > Coalesce(d.Start_Date, '1/1/1900')
精彩评论