开窗函数又叫
OLAP函数。
mysql没有开窗算法,等价写法:
select modify_name,shotel_id,modify_time,op_type
from
(
select if(@part1=ff.modify_name&&@part2=ff.shotel_id,@rank:=@rank+1,@rank:=1) rank,
@part1:=ff.modify_name modify_name,
@part2:=ff.shotel_id shotel_id,
ff.modify_time,ff.op_type
from
(
select a.modify_name,a.shotel_id ,a.modify_time, a.op_type
from temp_table a
where
op_type = 3
and
modify_time BETWEEN DATE_FORMAT('2016-04-10 00:00:00', '%Y-%m-%e %H:%i:%s')
AND DATE_FORMAT('2016-04-10 23:59:59', '%Y-%m-%e %H:%i:%s')
order by a.modify_name asc ,a.shotel_id asc,a.modify_time desc -- 排序
) ff,
(select @rank:=0,@part1='',@part2='') tt
) AA
where rank=1 ;
mysql没有开窗算法,等价写法:
select modify_name,shotel_id,modify_time,op_type
from
(
select if(@part1=ff.modify_name&&@part2=ff.shotel_id,@rank:=@rank+1,@rank:=1) rank,
@part1:=ff.modify_name modify_name,
@part2:=ff.shotel_id shotel_id,
ff.modify_time,ff.op_type
from
(
select a.modify_name,a.shotel_id ,a.modify_time, a.op_type
from temp_table a
where
op_type = 3
and
modify_time BETWEEN DATE_FORMAT('2016-04-10 00:00:00', '%Y-%m-%e %H:%i:%s')
AND DATE_FORMAT('2016-04-10 23:59:59', '%Y-%m-%e %H:%i:%s')
order by a.modify_name asc ,a.shotel_id asc,a.modify_time desc -- 排序
) ff,
(select @rank:=0,@part1='',@part2='') tt
) AA
where rank=1 ;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29665621/viewspace-2079149/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29665621/viewspace-2079149/