ORACLE数据库：表A 部门表 :字段：dept-id（部门ID） pid（上级单位ID） dept-name(部门名

select t11.organ_name "单位",
t11.dept_name "部门",
count(case when t14.lh_type=1 and t14.lh_kind=0 then 1 else null end) "新城多层",
count(case when t14.lh_type=1 and t14.lh_kind=1 then 1 else null end) "新城高层",
count(case when t14.lh_type=0 and t14.lh_kind=0 then 1 else null end) "老城多层",
count(case when t14.lh_type=0 and t14.lh_kind=1 then 1 else null end) "老城高层",
count(1) "小计"
from (select t1.dept_name organ_name,t2.dept_name,t2.dept_id
from (select dept_id,dept_name from a where dept_type = 1) t1,
(select pid,dept_id,dept_name
from A
where dept_type = 0) t2
where t1.dept_id = t2.pid) t11,
B t12,
C t13,
D t14
where t11.dept_id = t12.dept_id
and t12.user_id=t13.user_id
and t13.lh_id=t14.lh_id
group by t11.organ_name,
t11.dept_name