mysql按组排序并查询组里前两条记录的时间差

[毅拉罐]

表名adm_cplist

按cp_name分组 计算每个分组的d_time的最新的两条数据的时间差

[道玄希言]

SELECT DATEDIFF(t1.d_time, ifnull(t2.d_time, now())) 
(
SELECT T.* 
FROM
(
SELECT t1.*,
(SELECT COUNT(*)+1 FROM tbl01 WHERE cp_name=t1.cp_name AND d_time>t1.d_time) AS g_id
FROM adm_cplist t1
) T
WHERE T.g_id=1
) as t1
left join 
(
SELECT T.* 
FROM
(
SELECT t1.*,
(SELECT COUNT(*)+1 FROM tbl01 WHERE cp_name=t1.cp_name AND d_time>t1.d_time) AS g_id
FROM adm_cplist t1
) T
WHERE T.g_id=2
) as t2
on t1.cp_name = t2.cp_name

[Rotel-刘志东]

对mysql搞得这么复杂sql时吃不消的，mysql本着越简单越好。

[Rotel-刘志东]

特别对于数据量大的时候当时系统就完了。

[Rotel-刘志东]

引用 1 楼 yangb0803 的回复:

SELECT DATEDIFF(t1.d_time, ifnull(t2.d_time, now())) 
(
SELECT T.* 
FROM
(
SELECT t1.*,
(SELECT COUNT(*)+1 FROM tbl01 WHERE cp_name=t1.cp_name AND d_time>t1.d_time) AS g_id
FROM adm_cplist t1
) T
WHERE T.g_id=1
) as t1
left join 
(
SELECT T.* 
FROM
(
SELECT t1.*,
(SELECT COUNT(*)+1 FROM tbl01 WHERE cp_name=t1.cp_name AND d_time>t1.d_time) AS g_id
FROM adm_cplist t1
) T
WHERE T.g_id=2
) as t2
on t1.cp_name = t2.cp_name

分拆就做。

[ACMAIN_CHM]

select *,
(d_time-(select max(d_time) from adm_cplist where cp_name=t.cp_name and d_time<t.d_time)) as k
from adm_cplist t
where not exists (select 1 from adm_cplist where cp_name=t.cp_name and d_time>t.d_time)