create or replace view view_SJGQXX(gqid,gq,r_creationdate,r_isdelete,P_GPGUID,cljbd,clpd,zjd,cjd,sjjbxgls,sjpxgls,sjjp,sjpc) as
select t.gpguid as gqid,t.gq,t.r_creationdate,t.r_isdelete,t1.*,t2.sjpc,t2.sjjbxgls,t2.sjpxgls,t2.sjjp
from GP_GG_TB_GGGQJBXX t left outer join
(
select t.p_gpguid,
sum(case dslx when 1 then drwcds else 0 end) as cljbd,
sum(case dslx when 2 then drwcds else 0 end) as clpd,
sum(case dslx when 3 then drwcds else 0 end) as zjd,
sum(case dslx when 4 then drwcds else 0 end) as cjd
from (
select p_gpguid,dslx,sum (drwcds) as drwcds
from ( select t.p_gpguid,t.cxh,t1.drwcds,dslx from GP_GG_TB_GGCXJBXX t left outer join GP_GG_TB_CXJDTJ t1 on t.gpguid=t1.p_gpguid)
group by p_gpguid,dslx
) t group by p_gpguid
) t1
on t.gpguid=t1.p_gpguid
left outer join GP_GG_TB_GQSJXX_B t2
on t.gpguid=t2.p_gpguid
select * from gp_gg_tb_cxjdtj where
to_char(gp_gg_tb_cxjdtj.rq,'yyyy/mm/dd') =to_char(sysdate,'yyyy/mm/dd')