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_IDoracle full join on的用法,布布扣,bubuko.com
oracle full join on的用法
原文：http://blog.csdn.net/xiaoweiserver/article/details/24114879
