1。连环多表外联接
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 C.ADeviceID=D.DeviceID
AND D.ChangeType=0
AND C.BDeviceID=B.DeviceID(+)
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.CircuitID=G.Resid(+)
AND C.ADeviceID=P.DeviceID(+)
AND C.AIntDescr=P.PortDescr(+)
AND P.DeviceID=A.DeviceID(+)
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.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
AND C.ADeviceID=D.DeviceID AND D.ChangeType=0
外联接的关键是方向性
A left join B on ……,A=B(+)保留A的所有行,B中不匹配的设为NULL
以及连接条件和where条件
2. 外联接的 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
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
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