c2.sql

公务员管理系统

/*******************************
--创建对用人单位专项检查情况统计表视图
*******************************/
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
/