各位大侠,劳烦帮忙,先行谢过。我有一个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(+)中最后的(+)我也不清楚是什么意思?
解决方案 »
- 隆重推出最方便实用的国产免费的 Oracle 开发工具 - Oracle SQL Handler (免装客户端,跨平台能运行于LINUX及MAC,双语界面)
- oracle安装和PL/SQL developer工具联合使用的问题
- Oracle数据库客户端一般数据库工具有些什么啊?
- sql 语句的错误
- 新手求助
- 今日第三问:
- 如何修改数据库中的表名和数据字段。
- 请问linux下有没有免费的Oracle ODBC驱动??
- 当成功从令一台机器exp后,是否只要把dmp文件拿到自己的机器上导入,还是也要别的文件?
- SQL两表查询怎么也查不出来
- 索引与title='标题'效率那个高
- Oracle 如何插入 Byte
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);