ORACLE版的SQL文如下:select *
from (
select distinct a.CIRCUIT_NAME,a.circuit_id,b.arm_id,
(case when (b.arm_address>'0') then b.arm_address else b.arm_id end) as arm_address,
c.monitor_desc,c.monitor_id
from ca_circuit_info a,ca_arm_info b,ca_monitor_info c
where c.monitor_type='30' and c.circuit_id = a.circuit_id and
(c.circuit_id||'_'||c.arm_id) = b.arm_id) x , ca_tongdao_info d ,ca_user_work_area f
where x.monitor_id = d.monitor_id(+) and d.flag_id<='0' and x.circuit_id=f.circuit_id and f.logincode='10000000'
from (
select distinct a.CIRCUIT_NAME,a.circuit_id,b.arm_id,
(case when (b.arm_address>'0') then b.arm_address else b.arm_id end) as arm_address,
c.monitor_desc,c.monitor_id
from ca_circuit_info a,ca_arm_info b,ca_monitor_info c
where c.monitor_type='30' and c.circuit_id = a.circuit_id and
(c.circuit_id||'_'||c.arm_id) = b.arm_id) x , ca_tongdao_info d ,ca_user_work_area f
where x.monitor_id = d.monitor_id(+) and d.flag_id<='0' and x.circuit_id=f.circuit_id and f.logincode='10000000'
(+)->right join自己试试吧
from (
select distinct a.CIRCUIT_NAME,a.circuit_id,b.arm_id,
(case when (b.arm_address>'0') then b.arm_address else b.arm_id end) as arm_address,
c.monitor_desc,c.monitor_id
from ca_circuit_info a,ca_arm_info b,ca_monitor_info c
where c.monitor_type='30' and c.circuit_id = a.circuit_id and
concat(c.circuit_id,'_',c.arm_id) = b.arm_id) x left join ca_tongdao_info d on x.monitor_id = d.monitor_id
inner join ca_user_work_area f on x.circuit_id=f.circuit_id
where d.flag_id<='0' and f.logincode='10000000';
from (
select distinct a.CIRCUIT_NAME,a.circuit_id,b.arm_id,
if(b.arm_address>'0',b.arm_address,b.arm_id end) as arm_address,
c.monitor_desc,c.monitor_id
from ca_circuit_info a,ca_arm_info b,ca_monitor_info c
where c.monitor_type='30' and c.circuit_id = a.circuit_id and
concat(c.circuit_id,'_',c.arm_id) = b.arm_id) x , ca_user_work_area f
left join ca_tongdao_info d on x.monitor_id = d.monitor_id where d.flag_id<='0' and x.circuit_id=f.circuit_id and f.logincode='10000000'