有没有MySql达人帮我看下如何改写这个右连接

Question

这个原先用ORACLE的,现在要改用MySql数据库,对SQL文中的左右连接产生影响,由于原先SQL文也套得比较多,我试着改写几次都不成功,
请各位达人能不能帮我看看,如何改写,才能在MySql里正确通过运行..谢谢附ORACLE版SQL文:
select  DISTINCT  a.ca_company_id,a.ca_company_name,a.CIRCUIT_ID,a.CIRCUIT_NAME,a.arm_id, 
           a.arm_address,a.monitor_ID,a.monitor_desc,a.fix_config,a.TONGDAO_ID, a.TONGDAO_NAME,
           a.flag_id,a.DESCRIPTION 
from 
        (select T.ca_company_id,T.ca_company_name,T.CIRCUIT_ID,T.CIRCUIT_name,T.arm_id,T.arm_address,
                T.monitor_id,T.monitor_desc,T.fix_config,p2.tongdao_id,p2.tongdao_name,p2.flag_id,p2.description 
        from (
                select distinct p.ca_company_id,p.ca_company_name,p.CIRCUIT_ID,p.CIRCUIT_name,p.arm_id,
                          p.arm_address,p1.monitor_id,p1.monitor_desc,p1.fix_config 
               from ( 
                        select x.ca_company_id,x.ca_company_name,a.CIRCUIT_ID ,a.CIRCUIT_NAME,b.arm_id ,
                                (case when (b.arm_address>'0') then b.arm_address else b.arm_id end) as arm_address 
                        from ca_CIRCUIT_info a,ca_arm_info b,ca_company_info x  
                        where x.ca_company_id=a.company_id(+) and a.circuit_id=b.circuit_id(+) ) p,
                                  (select monitor_id,monitor_desc,fix_config,(CIRCUIT_ID||'_'||arm_id) as arm_id 
                                   from ca_monitor_info  
                                 where monitor_type='30') p1 
               where p.arm_id=p1.arm_id(+) ) T,ca_tongdao_info p2
        where T.monitor_id=p2.monitor_id(+) ) a,ca_user_work_area b 
where  a.CIRCUIT_ID=b.CIRCUIT_ID and b.logincode='200000001' 
order by CIRCUIT_ID,ARM_ID,MONITOR_desc

Answer 1

select  DISTINCT  a.ca_company_id,a.ca_company_name,a.CIRCUIT_ID,a.CIRCUIT_NAME,a.arm_id, 
          a.arm_address,a.monitor_ID,a.monitor_desc,a.fix_config,a.TONGDAO_ID, a.TONGDAO_NAME, 
          a.flag_id,a.DESCRIPTION 
from 
        (select T.ca_company_id,T.ca_company_name,T.CIRCUIT_ID,T.CIRCUIT_name,T.arm_id,T.arm_address, 
                T.monitor_id,T.monitor_desc,T.fix_config,p2.tongdao_id,p2.tongdao_name,p2.flag_id,p2.description 
        from ( 
                select distinct p.ca_company_id,p.ca_company_name,p.CIRCUIT_ID,p.CIRCUIT_name,p.arm_id, 
                          p.arm_address,p1.monitor_id,p1.monitor_desc,p1.fix_config 
              from ( 
                        select x.ca_company_id,x.ca_company_name,a.CIRCUIT_ID ,a.CIRCUIT_NAME,b.arm_id , 
                                (case when (b.arm_address>'0') then b.arm_address else b.arm_id end) as arm_address 
                        from ca_company_info x left join ca_CIRCUIT_info a on  x.ca_company_id=a.company_id 
                        left join ca_arm_info b on a.circuit_id=b.circuit_id) p
                        left join 
                                  (select monitor_id,monitor_desc,fix_config,concat(CIRCUIT_ID,'_',arm_id) as arm_id 
                                  from ca_monitor_info  
                                where monitor_type='30') p1 
              on p.arm_id=p1.arm_id) T left join ca_tongdao_info p2 
         on T.monitor_id=p2.monitor_id) a,ca_user_work_area b 
where  a.CIRCUIT_ID=b.CIRCUIT_ID and b.logincode='200000001' 
order by CIRCUIT_ID,ARM_ID,MONITOR_desc

Answer 2

先把老ORACLE版SQL文发到脚本里去,方便拷贝,观看select  DISTINCT  a.ca_company_id,a.ca_company_name,a.CIRCUIT_ID,a.CIRCUIT_NAME,a.arm_id, 
    a.arm_address,a.monitor_ID,a.monitor_desc,a.fix_config,a.TONGDAO_ID, a.TONGDAO_NAME,
    a.flag_id,a.DESCRIPTION 
from 
    (select T.ca_company_id,T.ca_company_name,T.CIRCUIT_ID,T.CIRCUIT_name,T.arm_id,T.arm_address,
            T.monitor_id,T.monitor_desc,T.fix_config,p2.tongdao_id,p2.tongdao_name,p2.flag_id,p2.description 
     from (
         select distinct p.ca_company_id,p.ca_company_name,p.CIRCUIT_ID,p.CIRCUIT_name,p.arm_id,
                p.arm_address,p1.monitor_id,p1.monitor_desc,p1.fix_config 
         from ( 
             select x.ca_company_id,x.ca_company_name,a.CIRCUIT_ID ,a.CIRCUIT_NAME,b.arm_id ,
                    (case when (b.arm_address>'0') then b.arm_address else b.arm_id end) as arm_address 
             from ca_CIRCUIT_info a,ca_arm_info b,ca_company_info x  
             where x.ca_company_id=a.company_id(+) and a.circuit_id=b.circuit_id(+) ) p,
                   (select monitor_id,monitor_desc,fix_config,(CIRCUIT_ID||'_'||arm_id) as arm_id 
                    from ca_monitor_info  
                    where monitor_type='30') p1 
         where p.arm_id=p1.arm_id(+) ) T,ca_tongdao_info p2
     where T.monitor_id=p2.monitor_id(+) ) a,ca_user_work_area b 
where  a.CIRCUIT_ID=b.CIRCUIT_ID and b.logincode='200000001' 
order by CIRCUIT_ID,ARM_ID,MONITOR_desc
验证了一下1楼朋友的SQL文,好像不大对,运行没问题,只是没有检索到数据...

Answer 3

那个是根据你的oracle语法进行改的
至于里面的数据是否有，你可以从里面最内层一层层往外分部测试查询就知道是什么原因没数据了

Answer 4

的确是Mysql数据库的数据问题，感谢1楼达人。