公司在做去o化改造,将oracle书库迁移到mysql,下面总结了sql语句改造的案例:
1.rownum 改造
select @rownnum:=@rownnum+1 AS rownum, e.* from (select @rownnum:=0) r,employee e
以上可以用来替换orcle的rownnum函数
2.wm_concat
可以用group_concat() ... group by id 例如 select id,group_concat(price separator ';') from goods group by id;
3.listagg()
SELECT
T .DEPTNO,
listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) names
FROM
SCOTT.EMP T
WHERE
T .DEPTNO = '20'
GROUP BY
T .DEPTNO
select T .DEPTNO, (group_concat(T.ENAME,order by T .DEPTNO )) names
from SCOTT.EMP T
WHERE
T .DEPTNO = '20'
GROUP BY
T .DEPTNO
4. decode
可以用case when代替
decode(a,b,c,d)替换为if(a=b,c,d)