oracle full join on的用法
浏览数：26 /
时间：2015年06月12日
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
--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
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
