调一个SQL查询bug,问题出在 ON ( s.CONT_ID = cname.CONT_ID OR s.suspect_cont_id = cname.CONT_ID )两个关系都满足会查询出来两条记录,如果两个关系都满足的话,取第一个条件该怎么处理啊?
select
sus.*,
(select ecifcustnbr from IP_EXPAN where ip_id=sus.cont_id)ecifId,
(select ecifcustnbr from IP_EXPAN where ip_id=sus.suspect_cont_id)SuspectEcifId,
(select executed_dt as BILLDATE from susopr where cont_id=sus.cont_id)LastBillDate,
(select executed_dt as BILLDATE from susopr where cont_id=sus.suspect_cont_id)SuspectLastBillDate,
(select opr from susopr where cont_id=sus.cont_id)contOpr,
(select opr from susopr where cont_id=sus.suspect_cont_id)susectOpr,
(SELECT CONTACT_NAME FROM CONTACT WHERE cont_id=sus.cont_id)PartyName,
(SELECT CONTACT_NAME FROM CONTACT WHERE cont_id=sus.suspect_cont_id)suspectPartyName
from (
select
s.suspect_id,
s.cont_id,
s.suspect_cont_id,
siml.mem ,
susp_reason_tp_cd,
ROWNUM
from
suspect s
left join SIML_EXPAN siml on siml.siml_id=s.suspect_id and siml.merge_split = '1'
inner join(SELECT CONT_ID FROM CONTACT c WHERE c.CONTACT_NAME = 'CNOOC China Limited') cname
ON ( s.CONT_ID = cname.CONT_ID OR s.suspect_cont_id = cname.CONT_ID )
LEFT JOIN susopr co ON co.cont_id = s.cont_id
LEFT JOIN susopr so ON so.cont_id = s.suspect_cont_id
Where
s.SUSP_ST_TP_CD = '1'
and (s.inactivated_dt is null or s.inactivated_dt>sysdate)
order by s.suspect_id desc ) sus
select
sus.*,
(select ecifcustnbr from IP_EXPAN where ip_id=sus.cont_id)ecifId,
(select ecifcustnbr from IP_EXPAN where ip_id=sus.suspect_cont_id)SuspectEcifId,
(select executed_dt as BILLDATE from susopr where cont_id=sus.cont_id)LastBillDate,
(select executed_dt as BILLDATE from susopr where cont_id=sus.suspect_cont_id)SuspectLastBillDate,
(select opr from susopr where cont_id=sus.cont_id)contOpr,
(select opr from susopr where cont_id=sus.suspect_cont_id)susectOpr,
(SELECT CONTACT_NAME FROM CONTACT WHERE cont_id=sus.cont_id)PartyName,
(SELECT CONTACT_NAME FROM CONTACT WHERE cont_id=sus.suspect_cont_id)suspectPartyName
from (
select
s.suspect_id,
s.cont_id,
s.suspect_cont_id,
siml.mem ,
susp_reason_tp_cd,
ROWNUM
from
suspect s
left join SIML_EXPAN siml on siml.siml_id=s.suspect_id and siml.merge_split = '1'
inner join(SELECT CONT_ID FROM CONTACT c WHERE c.CONTACT_NAME = 'CNOOC China Limited') cname
ON ( s.CONT_ID = cname.CONT_ID OR s.suspect_cont_id = cname.CONT_ID )
LEFT JOIN susopr co ON co.cont_id = s.cont_id
LEFT JOIN susopr so ON so.cont_id = s.suspect_cont_id
Where
s.SUSP_ST_TP_CD = '1'
and (s.inactivated_dt is null or s.inactivated_dt>sysdate)
order by s.suspect_id desc ) sus
改成这样试试呢:select
sus.*,
(select ecifcustnbr from IP_EXPAN where ip_id=sus.cont_id)ecifId,
(select ecifcustnbr from IP_EXPAN where ip_id=sus.suspect_cont_id)SuspectEcifId,
(select executed_dt as BILLDATE from susopr where cont_id=sus.cont_id)LastBillDate,
(select executed_dt as BILLDATE from susopr where cont_id=sus.suspect_cont_id)SuspectLastBillDate,
(select opr from susopr where cont_id=sus.cont_id)contOpr,
(select opr from susopr where cont_id=sus.suspect_cont_id)susectOpr,
(SELECT CONTACT_NAME FROM CONTACT WHERE cont_id=sus.cont_id)PartyName,
(SELECT CONTACT_NAME FROM CONTACT WHERE cont_id=sus.suspect_cont_id)suspectPartyName
from (
select
s.suspect_id,
s.cont_id,
s.suspect_cont_id,
siml.mem ,
susp_reason_tp_cd,
ROWNUM
from
suspect s
left join SIML_EXPAN siml on siml.siml_id=s.suspect_id and siml.merge_split = '1'
inner join(SELECT CONT_ID FROM CONTACT c WHERE c.CONTACT_NAME = 'CNOOC China Limited') cname
ON ( s.CONT_ID = cname.CONT_ID OR s.suspect_cont_id = cname.CONT_ID )
LEFT JOIN susopr co ON co.cont_id = cname.cont_id Where
s.SUSP_ST_TP_CD = '1'
and (s.inactivated_dt is null or s.inactivated_dt>sysdate)
order by s.suspect_id desc ) sus