查出了a表,然后对a表进行自查询,a表的别名t1,t2如同两张表,因为t1,t2查询的条件不一样,真的如同两张表,关联两张表,可以将两行或者多行数据合并成一行,不必使用wm_concat()函数。为了将t2表的数据全部查出来使用了右连接。

with a as(
select nsr.zgswj_dm, count(distinct nsr.djxh) cnt, 1 z
from hx_fp.fp_ly fp, hx_dj.dj_nsrxx nsr
where nsr.djxh = fp.djxh
and nsr.nsrzt_dm = '03'
and fp.lrrq >= date '2018-03-01'
and fp.lrrq <= date '2018-04-01'
AND
nsr.ZGSWSKFJ_DM LIKE '13609%'
group by nsr.zgswj_dm --order by zgswj_dm
union all
select dj.zgswj_dm, count(*) cnt, 2 z
from hx_dj.dj_nsrxx dj
where dj.nsrzt_dm = '03'
and dj.djrq <= date '2018-04-01'
AND
dj.ZGSWSKFJ_DM LIKE '13609%'
group by dj.zgswj_dm order by zgswj_dm )

select t2.zgswj_dm,nvl(t1.cnt,'0') cnt1,t2.cnt cnt2 from
(select * from a where z = '1') t1 right join (select * from a where z = '2') t2 on t1.zgswj_dm = t2.zgswj_dm

上一篇:touch事件学习


下一篇:nodejs 实践:express 最佳实践(四) express-session 解析