oracle full join on的用法

2022-03-28 14:02:47

create or replace view view_jyxkz_tj as
--yxq为有效期，ZXRQ为注销时间，FZRQ为发证日期
select t.dqxx_id,decode(t.yxq,‘‘,‘0001-01-01‘,t.yxq) as yxq, decode(t.zxrq,‘‘,‘0001‘,to_char

(t.zxrq,‘yyyy‘))

as zxrq,decode(t.fzrq,‘‘,‘0001‘,to_char(t.fzrq,‘yyyy‘)) as fzrq

from zm_busi_jyxkz_apply t;

====================================================================================
select AB.DQXX_ID ,AB.CZNUM,AB.XFNUM,C.ZXNUM
from (
select decode(A.DQXX_ID,‘‘,B.DQXX_ID,A.DQXX_ID) as DQXX_ID ,A.CZNUM,B.XFNUM

from (
select DQXX_ID,count(*) as CZNUM from
view_jyxkz_tj t
where t.fzrq<‘2013‘ and t.yxq>‘2013-01-01‘ and dqxx_id is not null
group by dqxx_id ) A

full join
(
select DQXX_ID,count(*) as XFNUM from
view_jyxkz_tj t
where t.fzrq=‘2012‘ and dqxx_id is not null
group by dqxx_id ) B

on A.DQXX_ID=B.DQXX_ID
) AB

full join
(
select DQXX_ID,count(*) as ZXNUM from
view_jyxkz_tj t
where t.zxrq=‘2012‘ and dqxx_id is not null
group by dqxx_id ) C

on AB.DQXX_ID=C.DQXX_ID

oracle full join on的用法,布布扣,bubuko.com

码农公寓

相关文章