急求：最新两行记录的时间字段值之差，非常感谢！

Question

表结构和数据如下：
id         ctime
20         2011-05-16 09:30:00
30         2011-05-16 09:10:00
20         2011-05-16 10:30:00
20         2011-05-16 19:30:00
30         2011-05-16 11:30:00
30         2011-05-16 12:30:00
我需要的结果是：
20        9小时
30        1小时也就是求最新2条时间记录值之差，如何用一条SQL语句表示出来，万分感谢！！

Answer 1

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[ctime] datetime)
insert [tb]
select 20,'2011-05-16 09:30:00' union all
select 30,'2011-05-16 09:10:00' union all
select 20,'2011-05-16 10:30:00' union all
select 20,'2011-05-16 19:30:00' union all
select 30,'2011-05-16 11:30:00' union all
select 30,'2011-05-16 12:30:00'
 
---查询---
select id,时间差=datediff(hh,min(ctime),max(ctime))
from(
select *,rn=row_number() over(partition by id order by ctime desc) from tb
)t
where rn between 1 and 2
group by id---结果---
id          时间差
----------- -----------
20          9
30          1(2 行受影响)

Answer 2

非常感谢josy的回复！我发错了板块，我这个问题是在Mysql环境下，使用select *,rn=row_number() over(partition by id order by ctime desc) from tb进行查询会出错，不知道有哪位兄弟知道在Mysql中的类似实现？谢谢！

Answer 3

SELECT a1.id,TIMEDIFF(a2.ctime,a1.ctime)
FROM (
SELECT a.* FROM tth4 a 
LEFT JOIN (SELECT id,MIN(ctime) AS mi FROM tth4 GROUP BY id) b 
ON a.id=b.id AND a.ctime=b.mi WHERE b.id IS NULL) a1
INNER JOIN
(
SELECT a.* FROM tth4 a 
LEFT JOIN (SELECT id,MIN(ctime) AS mi FROM tth4 GROUP BY id) b 
ON a.id=b.id AND a.ctime=b.mi WHERE b.id IS NULL) a2
ON a1.id=a2.id AND a1.ctime<a2.ctime

Answer 4

or
SELECT a1.id,TIMEDIFF(MAX(a1.ctime),MIN(a1.ctime)) AS 
FROM (
SELECT a.* FROM tth4 a  
LEFT JOIN (SELECT id,MIN(ctime) AS mi FROM tth4 GROUP BY id) b  
ON a.id=b.id AND a.ctime=b.mi WHERE b.id IS NULL) a1
GROUP BY a1.id

Answer 5

非常感谢WWWWA的回复！两次回复的答案经过测试完全满足需求！非常感谢！先结贴，我再好好消化下，再次感谢！

Answer 6

经过多次测试，WWWWA的方法有很大的局限性：每个id都必须有3条记录以上，若只有2条记录的话，就会不能满足需求。经在网上查找很多资料发现个更好、更简单的方法可以实现：select id,timediff(max(ctime),min(ctime)) from tth4 a where (
select count(1) from tth4 where id = a.id
and ctime >=a.ctime)<=2 
group by id

Answer 7

呵呵，举例也有学问
SET @num=1;
SET @mc='';
SELECT * FROM (
SELECT *,@num:=IF(@mc=id,@num+1,1) AS ss , @mc:=id FROM tth4 ORDER BY id,ctime) a WHERE ss<=2;