MySQL-mysql如何算出相邻记录的相隔天数？

Question

record表中有两个字段id,cdate，有如下记录
id cdate　
0800032955 2012-07-10
0800032955 2011-09-06
0800250910 2011-02-02
0800250910 2011-05-02
0800309603 2011-01-11
0800309603 2011-03-12
0800309603 2011-05-25

需要得到如下结果（注意0800309603 为两行，因为它有3个日期），如何写sql语句？
ID　　　　　　 diff_date
0800032955 *
0800250910 *
0800309603 *
0800309603 *

Answer 1

--测试数据库 oracle 10.2

--创建测试表
create table test(id varchar(20),cdate date);
--插入测试数据
insert into test
select '0800032955', to_date('2012-07-10', 'yyyy-mm-dd')
from dual
union all
select '0800032955', to_date('2011-09-06', 'yyyy-mm-dd')
from dual
union all
select '0800250910', to_date('2011-02-02', 'yyyy-mm-dd')
from dual
union all
select '0800250910', to_date('2011-05-02', 'yyyy-mm-dd')
from dual
union all
select '0800309603', to_date('2011-01-11', 'yyyy-mm-dd')
from dual
union all
select '0800309603', to_date('2011-03-12', 'yyyy-mm-dd')
from dual
union all
select '0800309603', to_date('2011-05-25', 'yyyy-mm-dd') from dual ;

--执行查询

select t1.id, t1.cdate, t2.cdate - t1.cdate as days
from (select id,
cdate,
row_number() over(PARTITION BY id ORDER BY cdate) as rn
from test) t1,
(select id,
cdate,
row_number() over(PARTITION BY id ORDER BY cdate) as rn
from test) t2
where t1.id = t2.id
and t1.rn = t2.rn - 1

--查询结果；

 ID CDATE DAYS
1 0800032955 2011-9-6 308
2 0800250910 2011-2-2 89
3 0800309603 2011-1-11 60
4 0800309603 2011-3-12 74

符合要求

Answer 2

SELECT t1.id,t1.cdate,max(t2.cdate),DATEDIFF(t1.cdate,max(t2.cdate))
FROM `t` t1
inner join t t2 on t1.id=t2.id and t1.cdate>t2.cdate
group by t1.id,t1.cdate

sql 还是很好玩的

Answer 3

如果用程序实现的，php里面有直接可以计算天数的函数。用数据库估计不太好实现吧?