各位大侠,劳烦帮忙,先行谢过。我有一个oracle的查询语句,我想要和到此查询语句对应的mysql的查询语句,因为我有一个oracle的表和一个mysql的表,两个表的字段是一样的,我想要用下面的查询语句在mysql中进行查询,但是里面有一些oracle特有的函数,所以想请教一下,mysql中该如何写呢?Oracle的查询语句如下:
select CALL_TYPE,USER_NUMBER,nvl(a.free_res_val1,0)+ nvl(a.free_res_val2,0)+ nvl(a.free_res_val3,0) free_res_val1
from JF.SMS_L a ,jf.dr_type_def c where 1=1 and ( a.dr_type=c.dr_type_id(+) )
and user_number = '1111111' and 1=1 and (dr_type = 101 and final_state = 1 );其中,主要是vnl函数mysql中没有,还有a.dr_type=c.dr_type_id(+)中最后的(+)我也不清楚是什么意思?
select CALL_TYPE,USER_NUMBER,nvl(a.free_res_val1,0)+ nvl(a.free_res_val2,0)+ nvl(a.free_res_val3,0) free_res_val1
from JF.SMS_L a ,jf.dr_type_def c where 1=1 and ( a.dr_type=c.dr_type_id(+) )
and user_number = '1111111' and 1=1 and (dr_type = 101 and final_state = 1 );其中,主要是vnl函数mysql中没有,还有a.dr_type=c.dr_type_id(+)中最后的(+)我也不清楚是什么意思?
from JF.SMS_L a ,jf.dr_type_def c where 1=1 and ( a.dr_type=c.dr_type_id(+) )
and user_number = '1111111' and 1=1 and (dr_type = 101 and final_state = 1 ); \\
改为:select CALL_TYPE,USER_NUMBER,ifnull(a.free_res_val1,0)+ ifnull(a.free_res_val2,0)+ ifnull(a.free_res_val3,0) free_res_val1
from JF.SMS_L a left outer join jf.dr_type_def c on a.dr_type=c.dr_type_id
where 1=1 and user_number = '1111111' and 1=1 and (dr_type = 101 and final_state = 1 );
USER_NUMBER,
coalesce(a.free_res_val1, 0) + nvl(a.free_res_val2, 0) +
coalesce(a.free_res_val3, 0) free_res_val1
from JF.SMS_L a
left outer join jf.dr_type_def c on a.dr_type = c.dr_type_id
where 1 = 1
-- and (a.dr_type = c.dr_type_id(+))
and user_number = '1111111'
and 1 = 1
and (dr_type = 101 and final_state = 1);
试试
a.dr_type(+)=c.dr_type_id表示左连接
USER_NUMBER,
CONCAT( coalesce(a.free_res_val1, 0)+ nvl(a.free_res_val2, 0) ,
coalesce(a.free_res_val3, 0)) free_res_val1
from JF.SMS_L a
left outer join jf.dr_type_def c on a.dr_type = c.dr_type_id
where 1 = 1
-- and (a.dr_type = c.dr_type_id(+))
and user_number = '1111111'
and 1 = 1
and (dr_type = 101 and final_state = 1);