以前同事让帮忙写个SQL,按分组查询最大和第二个值的差。虽然mysql没有oracle的rank over partition by函数,但是mysql中能在sql中使用变量,还是很强大的。最后结果如下:
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21124603/viewspace-767245/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21124603/viewspace-767245/