imei dat
"1111111111111111", "2013-07-04 13:12:12"
"1111111111111111", "2013-07-09 20:12:12"
"1111111111111111", "2013-07-01 12:12:12"
"1111111111111111", "2013-07-03 12:12:12"
"22222222222", "2013-07-20 20:12:12"
"22222222222", "2013-07-13 20:12:12"
"22222222222", "2013-07-09 20:12:12"
"22222222222", "2013-07-02 20:12:12"
"333333333", "2013-07-13 20:12:12"
"4444444444", "2013-07-17 21:12:12"
select imei user_id, max(max_dd) max_day, max(max_dd_2) second_day, to_days( max(max_dd)) - to_days(max(max_dd_2)) dd from (
select imei, max_dd， max_dd_2 from (
select
tmp.imei,
tmp.dat,
if(@imei=tmp.imei, @rank:=@rank+1,@rank:=1) as rank，
if(@rank = 1, @max_d := tmp.dat, @max_d := null) as max_dd,
if(@rank = 2, @max_d_2 := tmp.dat, @max_d_2 := null) as max_dd_2,
@imei:=tmp.imei
from (
select imei,
dat
from user_test
order by imei asc ,dat desc
) tmp ,
(select @rownum :=0 , @imei := null ,@rank:=0, @max_d :=null, @max_d_2 := null) a
) result
) t
group by imei
having count(*) > 1
user_di max_day second_day dd
"22222222222", "2013-07-20 20:12:12", "2013-07-13 20:12:12", 7
"1111111111111111", "2013-07-09 20:12:12", "2013-07-04 13:12:12", 5