mysql - SQL查询获取不同行的不同列之间的差异

Question

我有不同的记录，其中包含 Start Date 和 End Date 列。如果我按开始日期的升序对记录进行排序。并想连续找出一行的开始日期和前一行的结束日期之间的差异。

e.g.
Table: Data
------------
Date1           Date2           
13-DEC-2011     15-DEC-2011      
18-DEC-2011     16-DEC-2011     
21-DEC-2011     24-DEC-2011  

如果我有第三列，再查询一次说 ID，我想要按这些 ID 分组的差异例如

              ID          Date1             Date2           
              1     13-DEC-2011     15-DEC-2011      
              1     18-DEC-2011     16-DEC-2011     
              2     21-DEC-2011     24-DEC-2011  
              2     25-JAN-2012     25-FEB-2012
              2     29-FEB-2012     25-MAR-2012


              and I need :  


             ID INTERVAL FREE
              1 15 DEC to 18 DEC
              2 24dec to 25 jan;25 feb to 29 feb

Answer 1

这里是 SQLFiddle demo

with t1 as
(
select t.*,
        row_number() over (order by date1) rn 
        from t
)
select t1.date1 as d1,t1.Date2 as d2 ,
t2.Date2 as PreviousDate2,
t1.Date1-t2.Date2 as DIff
from t1
left join t1 t2 on t1.rn=t2.rn+1
order by t1.rn

这是回答您编辑的问题的查询:

SQLFiddle DEMO

如果您需要在一个逗号分隔的行中为每个 ID 收集行，您应该在客户端而不是在 SQL 中执行。

with t1 as
(
select t.*,
        row_number() over (partition by id order by date1) rn 
        from t
)
select t1.id,
t2.Date2 as PreviousDate2,
t1.date1 as d1
from t1
left join t1 t2 on (t1.rn=t2.rn+1) and (t1.id=t2.id)
where t2.Date2 is not null
order by t1.id,t1.rn