mysql实现oracle的decode和translate以及管道符拼接
发布时间:2020-08-14 03:58:04
来源:ITPUB博客
阅读:122
作者:贺子_DBA时代
目前要把网站整体业务迁移到云,并且又现在的oracle转换成mysql数据库,实现去ioe,现在有个任务是把oracle的一个视图在mysql中创建上,相关的基表已经创建完毕,想当然觉得只要把oracle的创建语句有拿出来,在mysql执行就可以了,其实真正过程遇到了很多问题,具体如下:
1,mysql 没有oracle的decode函数,
2,mysql t没有oracle的translate函数,
3,mysql create view 不能有子查询 ( 视图 第1349号错误解决方法)ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
我的解决办法是 视图中包含视图
4,mysql CONCAT_WS和CONCAT的区别
首先看一下oracle当前的视图创建sql:
create or replace view infoservice.mail_tasks as
select a.mid as member_id,a.dingyue_id as dingyue_id ,a.cust_email as cust_email,duration_days,
memberinfo.cust_right_group as level1,
'{"member_name":"'|| TRANSLATE (memberinfo.CUST_NAME,'''"','__') ||'","keyword":"'||
TRANSLATE (a.keyword,'''"','__')||'","table_name2":"'||a.topicid||'",
"area_id":"'||a.areaid||'","category_id":"'||a.industryid||'"}' as query
from
(
select t.record_id as dingyue_id ,t.member_id as mid,t.cust_email as cust_email,
t.duration_days as duration_days,t.keyword as keyword,t.table_name,
t.industryid,
t.areaid,
decode( t.topicid , ',' ,decode(t.table_name,'zbxx',',a,b,c,d,e,f,g,h,','xmxx',',i,j,k,'), t.topicid) as topicid
from infoservice.t_member_my t,infoservice.t_member_my_info i
where i.my_id='1'
and t.member_id=i.member_id and t.sign &