mysql> select * from abc; +----+---------------------+ | id | Update_time | +----+---------------------+ | 1 | 2009-05-04 15:02:09 | | 2 | 2009-05-04 15:09:15 | | 3 | 2009-05-04 15:15:45 | | 4 | 2009-05-04 15:45:19 | +----+---------------------+ 4 rows in set (0.00 sec)mysql> mysql> select a.id,b.id,TIMEDIFF(b.Update_time,a.Update_time) -> from abc a ,abc b -> where a.id=b.id-1; +----+----+---------------------------------------+ | id | id | TIMEDIFF(b.Update_time,a.Update_time) | +----+----+---------------------------------------+ | 1 | 2 | 00:07:06 | | 2 | 3 | 00:06:30 | | 3 | 4 | 00:29:34 | +----+----+---------------------------------------+ 3 rows in set (0.00 sec)mysql> select min(TIMEDIFF(b.Update_time,a.Update_time) ) -> from abc a ,abc b -> where a.id=b.id-1; +---------------------------------------------+ | min(TIMEDIFF(b.Update_time,a.Update_time) ) | +---------------------------------------------+ | 00:06:30 | +---------------------------------------------+ 1 row in set (0.00 sec)mysql>
mysql> select min(TIMEDIFF(b.Update_time,a.Update_time) ) -> from abc a ,abc b -> where a.id=b.id-1;
太好了,能否用一句mysql 语句实现呢?
二楼提供给你的不就是一句吗?mysql> select min(TIMEDIFF(b.Update_time,a.Update_time) ) -> from abc a ,abc b -> where a.id=b.id-1; +---------------------------------------------+ | min(TIMEDIFF(b.Update_time,a.Update_time) ) | +---------------------------------------------+ | 00:06:30 | +---------------------------------------------+ 1 row in set (0.00 sec)mysql>当您的问题得到解答后请及时结贴. http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
用IMMEDIFF即可 select a.id,b.id,TIMEDIFF(b.Update_time,a.Update_time) from abc a inner join abc b on a.id=b.id-1;
1、用有自增字段的临时表; 2、用变量累加形式: set @i=1 select min(TIMEDIFF(b.Update_time,a.Update_time)) from (select *,@i:=@i+1 as newid from tt) a inner join (select *,@i:=@i+1 as newid from tt) b on a.newid=b.newid-1;
2、 set @i=1; create table lsb as select *,@i:=@i+1 as newid from tt;select min(TIMEDIFF(b.Update_time,a.Update_time)) from lsb a inner join lsb b on a.newid=b.newid-1;
只用了4个字段,你自己修改一下 select a.bug_id,a.date_modified,a.id,a.user_id,min(b.date_modified), timediff(min(b.date_modified),a.date_modified) from ttgy a left join ttgy b on b.date_modified>a.date_modifiedgroup by a.bug_id,a.date_modified,a.id,a.user_id
ID(int类型) Update_time(Date类型)
1 2009-05-04 15:02:09
2 2009-05-04 15:09:15
3 2009-05-04 15:15:45
4 2009-05-04 15:45:19
要想用mysql语句直接查询出相邻时间差的最小值,如何写?例如: 1和2之间的时间差为:07:06
2和3 06:30
3和4 29:26
结果应该为: 06:30(也就是2和3之间是最小时间差)。
但是用mysql语句如何些出来呢?
在线急等,感激不尽!!!
+----+---------------------+
| id | Update_time |
+----+---------------------+
| 1 | 2009-05-04 15:02:09 |
| 2 | 2009-05-04 15:09:15 |
| 3 | 2009-05-04 15:15:45 |
| 4 | 2009-05-04 15:45:19 |
+----+---------------------+
4 rows in set (0.00 sec)mysql>
mysql> select a.id,b.id,TIMEDIFF(b.Update_time,a.Update_time)
-> from abc a ,abc b
-> where a.id=b.id-1;
+----+----+---------------------------------------+
| id | id | TIMEDIFF(b.Update_time,a.Update_time) |
+----+----+---------------------------------------+
| 1 | 2 | 00:07:06 |
| 2 | 3 | 00:06:30 |
| 3 | 4 | 00:29:34 |
+----+----+---------------------------------------+
3 rows in set (0.00 sec)mysql> select min(TIMEDIFF(b.Update_time,a.Update_time) )
-> from abc a ,abc b
-> where a.id=b.id-1;
+---------------------------------------------+
| min(TIMEDIFF(b.Update_time,a.Update_time) ) |
+---------------------------------------------+
| 00:06:30 |
+---------------------------------------------+
1 row in set (0.00 sec)mysql>
-> from abc a ,abc b
-> where a.id=b.id-1;
-> from abc a ,abc b
-> where a.id=b.id-1;
+---------------------------------------------+
| min(TIMEDIFF(b.Update_time,a.Update_time) ) |
+---------------------------------------------+
| 00:06:30 |
+---------------------------------------------+
1 row in set (0.00 sec)mysql>当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
select a.id,b.id,TIMEDIFF(b.Update_time,a.Update_time) from abc a inner join abc b on a.id=b.id-1;
1 2009-05-04 15:02:09
2 2009-05-04 15:09:15
3 2009-05-04 15:15:45
4 2009-05-04 15:45:19这个表是我子查询的虚表, id不是连续的,应该如何处理呢?
因为是mysql数据库,所以没有rownum控制,咋办?救命啊!等待回答!
2、用变量累加形式:
set @i=1
select min(TIMEDIFF(b.Update_time,a.Update_time)) from
(select *,@i:=@i+1 as newid from tt) a
inner join
(select *,@i:=@i+1 as newid from tt) b
on a.newid=b.newid-1;
set @i=1;
create table lsb as select *,@i:=@i+1 as newid from tt;select min(TIMEDIFF(b.Update_time,a.Update_time)) from
lsb a
inner join
lsb b
on a.newid=b.newid-1;
id user_id bug_id date_modified field_name old_value new_value type
2377 66 354 2009-06-24 15:57:05 1
2392 66 354 2009-06-26 10:00:05 status 90 20 0
2393 66 354 2009-06-26 10:00:40 severity 90 100 0
2388 66 354 2009-06-25 20:49:28 status 20 90 0
2387 66 354 2009-06-25 20:46:09 resolution 20 30 0
2386 66 354 2009-06-25 20:46:09 status 90 20 0
2385 66 354 2009-06-25 20:21:47 resolution 10 20 0
2384 66 354 2009-06-25 20:21:47 status 10 90 0
2383 66 354 2009-06-25 20:17:34 Ord 1 2 0
2394 66 354 2010-10-30 09:05:32 severity 100 90 0
+------+---------+---------------------+
| id | user_id | date_modified |
+------+---------+---------------------+
| 2377 | 66 | 2009-06-24 15:57:05 |
| 2383 | 66 | 2009-06-25 20:17:34 |
| 2384 | 66 | 2009-06-25 20:21:47 |
| 2385 | 66 | 2009-06-25 20:21:47 |
| 2386 | 66 | 2009-06-25 20:46:09 |
| 2387 | 66 | 2009-06-25 20:46:09 |
| 2388 | 66 | 2009-06-25 20:49:28 |
| 2392 | 66 | 2009-06-26 10:00:05 |
| 2393 | 66 | 2009-06-26 10:00:40 |
| 2394 | 66 | 2010-10-30 09:05:32 |
+------+---------+---------------------+
10 rows in set (0.00 sec)mysql> select * from t_zbwyd order by date_modified desc;
+------+---------+---------------------+
| id | user_id | date_modified |
+------+---------+---------------------+
| 2394 | 66 | 2010-10-30 09:05:32 |
| 2393 | 66 | 2009-06-26 10:00:40 |
| 2392 | 66 | 2009-06-26 10:00:05 |
| 2388 | 66 | 2009-06-25 20:49:28 |
| 2387 | 66 | 2009-06-25 20:46:09 |
| 2386 | 66 | 2009-06-25 20:46:09 |
| 2385 | 66 | 2009-06-25 20:21:47 |
| 2384 | 66 | 2009-06-25 20:21:47 |
| 2383 | 66 | 2009-06-25 20:17:34 |
| 2377 | 66 | 2009-06-24 15:57:05 |
+------+---------+---------------------+
10 rows in set (0.00 sec)mysql>
mysql> select min(TIMEDIFF(a.date_modified,b.date_modified))
-> from t_zbwyd a inner join t_zbwyd b on a.date_modified>=b.date_modified a
nd a.id!=b.id;
+------------------------------------------------+
| min(TIMEDIFF(a.date_modified,b.date_modified)) |
+------------------------------------------------+
| 00:00:00 |
+------------------------------------------------+
1 row in set, 9 warnings (0.00 sec)mysql>
select a.bug_id,a.date_modified,a.id,a.user_id,min(b.date_modified),
timediff(min(b.date_modified),a.date_modified) from ttgy a
left join ttgy b
on b.date_modified>a.date_modifiedgroup by a.bug_id,a.date_modified,a.id,a.user_id
2386 66 354 2009-06-25 20:46:09 status 90 20 0
select a.bug_id,a.date_modified,a.id,a.user_id,min(b.date_modified),
timediff(min(b.date_modified),a.date_modified) AS CB from ttgy a
left join ttgy b
on b.date_modified>a.date_modified group by a.bug_id,a.date_modified,a.id,a.user_id ;
+--------+---------------------+------+---------+----------------------+--------
---+
| bug_id | date_modified | id | user_id | min(b.date_modified) | CB
|
+--------+---------------------+------+---------+----------------------+--------
---+
| 354 | 2009-06-24 15:57:05 | 2377 | 66 | 2009-06-25 20:17:34 | 28:20:2
9 |
| 354 | 2009-06-25 20:17:34 | 2383 | 66 | 2009-06-25 20:21:47 | 00:04:1
3 |
| 354 | 2009-06-25 20:21:47 | 2384 | 66 | 2009-06-25 20:46:09 | 00:24:2
2 |
| 354 | 2009-06-25 20:21:47 | 2385 | 66 | 2009-06-25 20:46:09 | 00:24:2
2 |
| 354 | 2009-06-25 20:46:09 | 2386 | 66 | 2009-06-25 20:49:28 | 00:03:1
9 |
| 354 | 2009-06-25 20:46:09 | 2387 | 66 | 2009-06-25 20:49:28 | 00:03:1
9 |
| 354 | 2009-06-25 20:49:28 | 2388 | 66 | 2009-06-26 10:00:05 | 13:10:3
7 |
| 354 | 2009-06-26 10:00:05 | 2392 | 66 | 2009-06-26 10:00:40 | 00:00:3
5 |
| 354 | 2009-06-26 10:00:40 | 2393 | 66 | 2010-10-30 09:05:32 | 838:59:
59 |
| 354 | 2010-10-30 09:05:32 | 2394 | 66 | NULL | NULL
|
+--------+---------------------+------+---------+----------------------+--------
---+
10 rows in set, 1 warning (0.00 sec)
建议楼上按20楼的方法修改一下。 (其实也可以等楼主确认id 是自增,并且ID大的一定时间也大,这样就可以直接 a.id>b.id了)另外楼主的要求只是拿到最小的故障间隔,他要的只是一个数字(当然也是猜的,也是为什么建议楼主贴出自己期望的结果的原因,否则不同的人对同一个问题有不同的理解。)