MYSQL行号使用实例

select fk_device_id, DEVICE_NO, CUR_VALUE, GIS_LONG, GIS_LAT, C_TIME
from (select fk_device_id,
C_TIME,
GIS_LONG,
GIS_LAT,
if((@device_no = DEVICE_NO or @device_no is null) and
((CUR_VALUE = 0 and
(@cur_value <> 0 or @cur_value is null)) or
(CUR_VALUE <> 0 and @cur_value = 0)),
@rank := 1,
@rank := 0) as rownum,
@device_no := DEVICE_NO as DEVICE_NO,
@cur_value := CUR_VALUE as CUR_VALUE
from (select s.fk_device_id,
s.DEVICE_NO,
s.C_TIME,
s.CUR_VALUE,
d.GIS_LONG,
d.GIS_LAT
from t_device_speed s
inner join t_device_gps d
on s.C_TIME = d.C_TIME
where s.c_time >=
(select date_sub(curdate(), interval 3 day))
and s.DEVICE_NO = ? limit 0, 1000
union
select s.fk_device_id,
s.DEVICE_NO,
s.C_TIME,
s.CUR_VALUE,
d.GIS_LONG,
d.GIS_LAT
from t_device_speed_history s
inner join t_device_gps_history d
on s.C_TIME = d.C_TIME
where s.c_time >=
(select date_sub(curdate(), interval 3 day))
and s.DEVICE_NO = ? limit 0, 1000) temp,
(select @rank := 0, @device_no := null, @cur_value := null) ranktemp
order by c_time) tmepresult
where rownum = 1
order by c_time