Oracle 分析函数之row_number()， over

1：Oracle 分析函数row_number()用mysql实现

SELECT module, machine, time, @row:=@row+1 rownum

FROM total_freq_ctrl, (select @row:=0) b 

order by module,machine,time desc

2:Oracle 分析函数over (partition by job order by id desc ) 的 Mysql实现

oracle 开窗函数，对结果集已sex分组并已id排序

select * from (
select name,if(@temp=job,@rank:=@rank+1,@rank:=1) as rank,  
@temp:=job from (select * from emp) a, (select  @temp:= null ,@rank:=0) b
) c where rank=1  order by id desc

if 解释：会对结果逐个判断，满足就加一，不满足就新起一个

另一种高效写法

select * from (
SELECT name，
       case when @mid = jobthen @row:=@row+1 else @row:=1 end rownum,
       @mid:=job
FROM heyf_t10
order by job,salary desc) tt where tt.rownum=1;

类似于下图效果

附另一种实现方式 ：https://blog.csdn.net/Howie_zhw/article/details/51706282

3：Oracle计算百分比并保留两位小数-mysql实现

oracle

select round(6/3335,4)    from  dual;
select round(6/3335,4)*100.00||'%'    from  dual;
select to_char(round(6/3335,4)*100.00,'fm90.09')||'%'    from  dual;

mysql

select concat(format(round(6/3335,4)*100.00,4),2),'%' )   from  dual;

4：mysql类型转换

convert（value,type） 
case（ 字段  int）