定编定岗

自己看

select z.one as one,
			 z.two as two,
			 z.three as three,
			 z.pk_org as pk_org,
			 z.pk_group as pk_group,
			 org.name as orgname,
			 z.postname as postname,
			 TRIM (Replace(to_char(wmsys.wm_concat(z.trueclass||' ')),',',' ')) as trueclass,
			 TRIM(Replace(to_char(wmsys.wm_concat(z.oneclass||' ')),',',' ') )as oneclass,
			 TRIM(Replace(to_char(wmsys.wm_concat(z.twoclass||' ')),',',' ')) as twoclass,
			 TRIM(Replace(to_char(wmsys.wm_concat(z.threeclass||' ')),',',' ')) as threeclass,
			 e.con as sjrs,
			 b.con as sjzrs
from(

	select x.one as one,
				 x.two as two,
				 x.pk_org as pk_org,
				 x.pk_group as pk_group,
				 x.three as three,
				 x.postname as postname,
				 x.deptid as deptid,
				 x.postid as postid,
				to_char(case x.defdoc when '1001A810000000004UE3' then wmsys.wm_concat(x.name||' ') else null end) as threeclass,
				to_char(case x.defdoc when '1001A810000000004UE2' then wmsys.wm_concat(x.name||' ') else null end) as twoclass,
				to_char(case  x.defdoc when '1001T4100000002A4ORQ' then wmsys.wm_concat(x.name||' ')else null end)as trueclass,
				to_char(case x.defdoc when '1001A810000000004UE1' then wmsys.wm_concat(x.name||' ') else null end)as oneclass
	FROM 
	(
		SELECT
			(case when onedept.name is null then (case when twodept.name is null then dept.name else twodept.name end) else onedept.name end) as one,
			(case when onedept.name is null then (case when twodept.name is null then twodept.name else dept.name end) else twodept.name end) as two,
			(case when onedept.name is null then onedept.name else dept.name end)  as three,
			a.name as name,
			a.pk_org as pk_org,
			a.pk_group as pk_group,
			dept.pk_dept as deptid,
			post.pk_post as postid,
			dept.name as dename,
			post.postname as postname,
			bc.pk_defdoc as defdoc,
			bc.name as class
		FROM
			bd_psndoc a --人员基本信息
		LEFT JOIN hi_psnjob psnjob --人员工作信息
			ON a. code =psnjob.clerkcode  
		LEFT JOIN om_post post
			on psnjob.pk_post =post.pk_post
			LEFT JOIN org_dept dept --部门
			on post.pk_dept =dept.pk_dept
		LEFT JOIN org_dept twodept
			on dept.pk_fatherorg =twodept.pk_dept
		LEFT JOIN org_dept onedept
			on twodept.pk_fatherorg =onedept.pk_dept
		LEFT JOIN bd_defdoc bc 
			on a.glbdef25=bc.pk_defdoc
		where psnjob.endflag='N' and psnjob.lastflag='Y'
	) x
	group BY  x.one,x.two,x.three,x.postname,x.defdoc, x.postid,x.deptid, x.pk_org,x.pk_group) z
	LEFT JOIN (
		select dept.name name,COUNT(dept.name) con,dept.pk_dept de from 
			bd_psndoc a --人员基本信息
			LEFT JOIN hi_psnjob psnjob --人员工作信息
			ON a. code =psnjob.clerkcode  
			LEFT JOIN om_post post
				on psnjob.pk_post =post.pk_post
				LEFT JOIN org_dept dept --部门
				on post.pk_dept =dept.pk_dept
			LEFT JOIN org_dept twodept
				on dept.pk_fatherorg =twodept.pk_dept
			LEFT JOIN org_dept onedept
				on twodept.pk_fatherorg =onedept.pk_dept
			where psnjob.endflag='N' and psnjob.lastflag='Y'
				group by dept.name,dept.pk_dept
) b
	on z.deptid=b.de
LEFT JOIN (
		select post.postname name,COUNT(post.postname) con,post.pk_post de from 
			bd_psndoc a --人员基本信息
			LEFT JOIN hi_psnjob psnjob --人员工作信息
			ON a. code =psnjob.clerkcode  
			LEFT JOIN om_post post
				on psnjob.pk_post =post.pk_post
				LEFT JOIN org_dept dept --部门
				on post.pk_dept =dept.pk_dept
			LEFT JOIN org_dept twodept
				on dept.pk_fatherorg =twodept.pk_dept
			LEFT JOIN org_dept onedept
				on twodept.pk_fatherorg =onedept.pk_dept
			where psnjob.endflag='N' and psnjob.lastflag='Y'
				group by dept.name,post.pk_post,post.postname
) e	
on z.postid=e.de
LEFT JOIN org_orgs org
on z.pk_org=org.pk_org
GROUP BY z.one,z.two,z.three,z.postname,e.con,b.con,z.pk_org,z.pk_group,org.name