/*******************************
--創(chuàng)建對用人單位專項檢查情況統(tǒng)計表視圖
*******************************/
create or replace view v_c2 as
select a.aj_id aj_id,a.qx qx,a.dwid dwid,dp_sys.f_get_dwmc_by_dwid(a.dwid) dwmc
      ,a.cbr cbr,dp_sys.f_get_ryxm_by_ryid(a.cbr) rymc
			,decode(dp_sys.f_get_zbdm_by_cbr(a.cbr),null,'99999999',dp_sys.f_get_zbdm_by_cbr(a.cbr)) zbdm,decode(dp_sys.f_get_zbmc_by_zbdm(dp_sys.f_get_zbdm_by_cbr(a.cbr)),null,'無組別人員',dp_sys.f_get_zbmc_by_zbdm(dp_sys.f_get_zbdm_by_cbr(a.cbr))) zbmc
			,a.aj_djsj sj,a.scajsj sbsj ,a.aj_flag aj_flag,b.jcid jcid
	from aj_djb a,zf_jcxx b
 where a.jcid = b.jcid(+) and b.jctj = '01'
/
--上報群眾舉報件辦理情況明細表
select zbmc,dwmc,rymc,djsj,sbsj 
	from v_c2
 where 1=1
/
--群眾舉報件辦理情況匯總表
select zbmc 
	from v_c2
 where 1=1
 group by zbmc
/
select zbdm,count(aj_id) jbjs
	from v_c2
 where 1=1
 group by zbdm order by zbdm
/
select zbdm,count(aj_id) sbjs
	from v_c2
 where 1=1
 group by zbdm
/