外联接小结

１。连环多表外联接

select *

FROM Circuit C,Device D,ResGroup G,

     PortInfo P,DevAddr A,Device B,DevAddr BA

WHERE C.CircuitID='$cirid'

  AND (C.ChangeType=0 OR C.ChangeType=-1) 
  AND C.ADeviceID=D.DeviceID

  AND D.ChangeType=0 
  AND C.BDeviceID=B.DeviceID(+)

  AND (B.ChangeType=0 OR B.ChangeType IS NULL) 
  AND C.CircuitID=G.Resid(+) 
  AND C.ADeviceID=P.DeviceID(+) 

  AND C.AIntDescr=P.PortDescr(+) 
  AND P.DeviceID=A.DeviceID(+)

  AND P.PortDescr=A.IntDescr(+)

  AND (A.ChangeType=0 OR A.ChangeType IS NULL) 
  AND C.BDeviceID=BA.DeviceID(+)

  AND C.BIntDescr=BA.IntDescr(+)

  AND (BA.ChangeType=0 OR BA.ChangeType IS NULL)

 

体现出的外连接关系

C=B(+)

C=G(+)

C=BA(+)

C=P(+)

P=A(+)

即最后得出由C决定的外联接大表：[[[[(A(+)=P](+)=C]=B(+)]=G(+)]=BA(+)

 

不能忽略的一点，即外联接有由 ON 决定的外联接条件以及由where决定的select条件

 

得到的修改语句为：

select *

from Device D,

      DevAddr A right join PortInfo P

               on P.DeviceID=A.DeviceID AND P.PortDescr=A.IntDescr

                     AND A.ChangeType=0

       right join Circuit C

               on C.ADeviceID=P.DeviceID AND C.AIntDescr=P.PortDescr

       left join Device B

               on  C.BDeviceID=B.DeviceID

                     AND B.ChangeType=0

       left join ResGroup G

               on C.CircuitID=G.Resid

      left join C.BDeviceID=BA.DeviceID AND C.BIntDescr=BA.IntDescr

                     AND BA.ChangeType=0

WHERE C.CircuitID='$cirid'

  AND (C.ChangeType=0 OR C.ChangeType=-1) 
  AND C.ADeviceID=D.DeviceID AND D.ChangeType=0 

 

外联接的关键是方向性

A left join B on ……，A=B（+）保留A的所有行，B中不匹配的设为NULL

以及连接条件和where条件

２. 外联接的 or is null 含义

 A.comID = B.comID(+)  表示： B.comID = A.comID  or B.comID is null

外联接与group by 联合使用 ：

表间关系：

prov 存有各省及全国中心公司的ID、名称

city  存有各省下子公司，及中心公司ID、名称 

二者关联关系：C.mortherID = P.comID

appealMailList 投诉邮件详细表

A表在规定时间段内可能没有关于某comID的投诉，所以应用外联接，所有与A表有关的字段都要进行外联接

sql中以 A.ipTypeid 分组，( 1 =>动态 2 => 静态  )

        以P.comID分组，某个省（如湖北）下的某个地市可能没有投诉，故在应用外联接时有A.ipTypeid is null 的情况 ，所以分组结果为 iptype = 1 ,iptype =2 iptype is null 三种情况

sql ：

select P.Compid,
       P.Compname,
       A.Iptypeid,
       count(A.SEQUENCENUM)
from prov P,
     city C,
     appealMailList A
where C.Mothercompid = P.Compid and
      C.Compid = A.Compid(+) and
      to_char(A.Sendtime(+),'YYYYMMDD') between '20070201' and '20070209'
group by P.Compid,P.COMPNAME,A.Iptypeid
order by P.Compid,P.COMPNAME,A.Iptypeid

结果如下：

  comid       comname          iptype  countNum

1 CT00000 中国电信集团中心 1         2
2 CT00012 江苏电信               1           1
3 CT00012 江苏电信              0
4 CT00030 湖北电信              1            9
5 CT00030 湖北电信              2           6
6 CT00030 湖北电信              0
7 CT00064 安徽电信             0
8 CT00065 北京电信               0
9 CT00067 福建电信              0
10 CT00087 河北电信             0