drop table if exists a_test; create table `a_test` ( `ii` bigint(20) unsigned NOT NULL auto_increment, `LDATE` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, primary key(`ii`) )default charset=utf8;mysql> desc a_test; +-------+---------------------+------+-----+-------------------+---------------- + | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+-------------------+---------------- + | ii | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | LDATE | timestamp | NO | | CURRENT_TIMESTAMP | | +-------+---------------------+------+-----+-------------------+---------------- + 2 rows in set (0.02 sec)按照LDATE来排序,降序排序LDATE,查询前两条记录的日期差,之间相差多久
insert into a_test(LDATE) values('2012-01-02'); insert into a_test(LDATE) values('2012-02-10'); insert into a_test(LDATE) values('2012-03-08'); insert into a_test(LDATE) values('2012-06-12'); insert into a_test(LDATE) values('2012-06-15'); insert into a_test(LDATE) values('2012-11-02');
select a.ii,b.ii , TIMESTAMPDIFF(b.LDATE,a.LDATE) from a_test a JOIN a_test b on a.ii = b.ii-1以上。
create table `a_test` (
`ii` bigint(20) unsigned NOT NULL auto_increment,
`LDATE` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
primary key(`ii`)
)default charset=utf8;mysql> desc a_test;
+-------+---------------------+------+-----+-------------------+----------------
+
| Field | Type | Null | Key | Default | Extra
|
+-------+---------------------+------+-----+-------------------+----------------
+
| ii | bigint(20) unsigned | NO | PRI | NULL | auto_increment
|
| LDATE | timestamp | NO | | CURRENT_TIMESTAMP |
|
+-------+---------------------+------+-----+-------------------+----------------
+
2 rows in set (0.02 sec)按照LDATE来排序,降序排序LDATE,查询前两条记录的日期差,之间相差多久
insert into a_test(LDATE) values('2012-02-10');
insert into a_test(LDATE) values('2012-03-08');
insert into a_test(LDATE) values('2012-06-12');
insert into a_test(LDATE) values('2012-06-15');
insert into a_test(LDATE) values('2012-11-02');
from a_test a
JOIN a_test b on a.ii = b.ii-1以上。
是的,sql没测试过,只是大致写了下过程