sql语句（一）统计跟单位目录关联的人员信息表的多个数据

dwml表：dwdm层次码，dwmc单位名称，
 ryjbxx表，rymc人员名称，dwdm：所属单位，zc：职称：01教授，02教师，求xys：现有人数，教授人数：jshrs，教师人数：jsrs。
单位代码层次码： xys   jshrs  jsrs
01                      
0101
0102
0103

02
0201
0202
0303

 select dwml.dwdm as dwdm,dwml.dwmc as dwmc,
 nvl(sum(t.xys),0) as xys,
 nvl(sum(t.jshrs),0) as jshrs,
 nvl(sum(t.jsrs),0) as jsrs,
 
 from dwml left outer join
 
 (---4
  select t1.dwdm as dwdm,t1.xys as xys,t2.jshrs as jshrs,t3.jsrs as jsrs from
  (---1
   select dwdm,nvl(count(dwdm),0) as xys from ryjbxx  group by dwdm

  )---1
  t1 left outer join
  (---2
   select dwdm,nvl(count(zc),0) as jshrs from ryjbxx where zc ='01' group by dwdm

  )---2
  t2 on t1.dwdm=t2.dwdm left outer join
  (---3
   select dwdm,nvl(count(zc),0) as jsrs from ryjbxx where zc ='02' group by dwdm

  )---3
  t3 on t1.dwdm=t3.dwdm
 )---4
 t on dwml.dwdm=substr(t.dwdm,len(dwml.dwdm)) group by dwml.dwdm ,dwml.dwmc