首先将两上相同 地表放在一起,在前一个表里面预留列位,同时后一个表也要预留列位,这样整体查询出来地数据都会相应补位。
select a.dwjc,NVL(sum(a.jcz), 0) jcz,
NVL(sum(a.jsq), 0) jsq,
NVL(sum(a.jdw), 0) jdw,
NVL(sum(a.jxx), 0) jxx,
NVL(sum(a.gj), 0) gj,
NVL(sum(a.sj), 0) sj,
NVL(sum(a.dq), 0) dq,
nvl(sum(a.jcz + a.jsq + a.jdw + a.jxx), 0) ct
from(
select b.dwjc,
nvl((CASE
WHEN lb = 1 then
(select count(id)
from xc_fiveenterbase
where lb = a.lb
and lrdw = b.dwdm)
else
0
end),
0) as jcz,
nvl((CASE
WHEN lb = 2 then
(select count(id)
from xc_fiveenterbase
where lb = a.lb
and lrdw = b.dwdm)
else
0
end),
0) as jsq,
nvl((CASE
WHEN lb = 3 then
(select count(id)
from xc_fiveenterbase
where lb = a.lb
and lrdw = b.dwdm)
else
0
end),
0) as jdw,
nvl((CASE
WHEN lb = 4 then
(select count(id)
from xc_fiveenterbase
where lb = a.lb
and lrdw = b.dwdm)
else
0
end),
0) as jxx,
nvl(0, 0) gj,
nvl(0, 0) sj,
nvl(0, 0) dq
from (select *
From xc_fiveenterbase
where lrdw like '52%'
and lrsj >= to_date('2011-03-01', 'yyyy-MM-dd')
and lrsj <= to_date('2011-08-04', 'yyyy-MM-dd')) a,
ud_unit b
where a.lrdw = b.dwdm(+)
group by b.dwjc, dwdm, lb
union all
select b.dwjc,
nvl(0, 0) jcz,
nvl(0, 0) jsq,
nvl(0, 0) jdw,
nvl(0, 0) jxx,
nvl((CASE
WHEN jb = 1 then
(select count(id)
from xc_fiveenterbase
where jb = a.jb
and lrdw = b.dwdm)
else
0
end),
0) as gj,
nvl((CASE
WHEN jb = 2 then
(select count(id)
from xc_fiveenterbase
where jb = a.jb
and lrdw = b.dwdm)
else
0
end),
0) as sj,
nvl((CASE
WHEN jb = 3 then
(select count(id)
from xc_fiveenterbase
where jb = a.jb
and lrdw = b.dwdm)
else
0
end),
0) as dq
from (select *
From xc_fiveenterbase
where lrdw like '52%'
and lrsj >= to_date('2011-03-01', 'yyyy-MM-dd')
and lrsj <= to_date('2011-08-04', 'yyyy-MM-dd')) a,
ud_unit b
where a.lrdw = b.dwdm
group by b.dwjc, dwdm, jb
) a group by a.dwjc order by sj
select a.dwjc,NVL(sum(a.jcz), 0) jcz,
NVL(sum(a.jsq), 0) jsq,
NVL(sum(a.jdw), 0) jdw,
NVL(sum(a.jxx), 0) jxx,
NVL(sum(a.gj), 0) gj,
NVL(sum(a.sj), 0) sj,
NVL(sum(a.dq), 0) dq,
nvl(sum(a.jcz + a.jsq + a.jdw + a.jxx), 0) ct
from(
select b.dwjc,
nvl((CASE
WHEN lb = 1 then
(select count(id)
from xc_fiveenterbase
where lb = a.lb
and lrdw = b.dwdm)
else
0
end),
0) as jcz,
nvl((CASE
WHEN lb = 2 then
(select count(id)
from xc_fiveenterbase
where lb = a.lb
and lrdw = b.dwdm)
else
0
end),
0) as jsq,
nvl((CASE
WHEN lb = 3 then
(select count(id)
from xc_fiveenterbase
where lb = a.lb
and lrdw = b.dwdm)
else
0
end),
0) as jdw,
nvl((CASE
WHEN lb = 4 then
(select count(id)
from xc_fiveenterbase
where lb = a.lb
and lrdw = b.dwdm)
else
0
end),
0) as jxx,
nvl(0, 0) gj,
nvl(0, 0) sj,
nvl(0, 0) dq
from (select *
From xc_fiveenterbase
where lrdw like '52%'
and lrsj >= to_date('2011-03-01', 'yyyy-MM-dd')
and lrsj <= to_date('2011-08-04', 'yyyy-MM-dd')) a,
ud_unit b
where a.lrdw = b.dwdm(+)
group by b.dwjc, dwdm, lb
union all
select b.dwjc,
nvl(0, 0) jcz,
nvl(0, 0) jsq,
nvl(0, 0) jdw,
nvl(0, 0) jxx,
nvl((CASE
WHEN jb = 1 then
(select count(id)
from xc_fiveenterbase
where jb = a.jb
and lrdw = b.dwdm)
else
0
end),
0) as gj,
nvl((CASE
WHEN jb = 2 then
(select count(id)
from xc_fiveenterbase
where jb = a.jb
and lrdw = b.dwdm)
else
0
end),
0) as sj,
nvl((CASE
WHEN jb = 3 then
(select count(id)
from xc_fiveenterbase
where jb = a.jb
and lrdw = b.dwdm)
else
0
end),
0) as dq
from (select *
From xc_fiveenterbase
where lrdw like '52%'
and lrsj >= to_date('2011-03-01', 'yyyy-MM-dd')
and lrsj <= to_date('2011-08-04', 'yyyy-MM-dd')) a,
ud_unit b
where a.lrdw = b.dwdm
group by b.dwjc, dwdm, jb
) a group by a.dwjc order by sj