<!-- 人事管理 -->
男女教师统计
select sum(case when sex = '1' then 1 else 0 end) || ',' ||
sum(case when sex = '2' then 1 else 0 end) as sex from T_RS_EMPLOYEE
教师年龄统计
select
count(t.age) || ',' ||
count(case
when t.age between 20 and 30 then
'20-30'
end
) || ',' ||
count(case
when t.age between 31 and 40 then
'31-40'
end
) || ',' ||
count(case
when t.age between 41 and 50 then
'41-50'
end
) || ',' ||
count(case
when t.age >50 then
'>50'
end
) as age
from
(select trunc((sysdate-birthday)/365,0) as age from T_RS_EMPLOYEE where birthday is not null) t
教职工状态
select wmsys.wm_concat(x.dic_name) as jzgzt from (select t.dic_name from T_SYS_DIC t where t.parent_id = '653a307f-8b54-4e0d-952f-9e0114f0111a') x
教职工状态对应人数 //jzgztstatus 在bean里不能写成jzgzt_status ,不识别bean里面的下划线
select
sum(case when n.staff_status= '200' then 1 else 0 end) || ',' ||
sum(case when n.staff_status= '201' then 1 else 0 end) || ',' ||
sum(case when n.staff_status= '202' then 1 else 0 end) || ',' ||
sum(case when n.staff_status = '203' then 1 else 0 end) || ',' ||
sum(case when n.staff_status = '204' then 1 else 0 end) || ',' ||
sum(case when n.staff_status = '205' then 1 else 0 end) || ',' ||
sum(case when n.staff_status = '206' then 1 else 0 end) || ',' ||
sum(case when n.staff_status = '207' then 1 else 0 end) || ',' ||
sum(case when n.staff_status = '208' then 1 else 0 end) || ',' ||
sum(case when n.staff_status = '209' then 1 else 0 end) || ',' ||
sum(case when n.staff_status = '210' then 1 else 0 end) || ',' ||
sum(case when n.staff_status = '211' then 1 else 0 end) || ',' ||
sum(case when n.staff_status = '299' then 1 else 0 end) || ',' ||
sum(case when n.staff_status = '100' then 1 else 0 end)
as jzgztstatus from
(select t.staff_status from T_RS_EMPLOYEE t where t.staff_status is not null) n
//部门教职工男女比例
select wmsys.wm_concat(o.sex) as deptsex from (select
i.deptdata || ',' || sum(case when i.sex = '1' then 1 else 0 end) || ',' ||
sum(case when i.sex = '2' then 1 else 0 end) as sex
from (select u.dept_name as deptdata,s.sex from T_RS_EMPLOYEE_DEPT t,T_RS_EMPLOYEE s,T_SYS_DEPT u
where t.person_id = s.person_id and u.dept_id = t.dept_id and s.sex is not null) i group by i.deptdata) o
<!-- 协同办公 -->
//各部门通知公告统计
select wmsys.wm_concat(a.notice) as notice from
(select d.dept_name || ',' || sum(case when t.type = '1' then 1 else 0 end) || ',' ||
sum(case when t.type = '2' then 1 else 0 end) as notice from
T_SYS_NOTICE t,T_SYS_DEPT d where t.dept_id = d.dept_id group by d.dept_name) a
//各部门当前年通知公告统计
select wmsys.wm_concat(w.noticetype) as oldnotice from
(select s.olddate || ',' || sum(case when s.type = '1' then 1 else 0 end) || ',' ||
sum(case when s.type = '2' then 1 else 0 end) as noticetype
from (select
substr(to_char(t.public_time,'YYYY-MM-DD'),1,7) as olddate,t.type
from T_SYS_NOTICE t where t.public_time is not null) s group by s.olddate) w
//各专业课程数统计
select wmsys.wm_concat(h.dic_name || ',' || sum(m.total_hours)) as zycourse from
(select t.major_type,r.total_hours
from T_XG_MAJOR t,T_JW_COURSE s,T_JW_COURSEPLAN_DETAILS r
where t.major_code = s.major_code and r.course_id = s.course_id) m,
(select t.dic_name,t.dic_code from T_SYS_DIC t where
t.parent_id = (select id from T_SYS_DIC where dic_code = 'ZXZYLX')) h
where m.major_type = h.dic_code group by h.dic_name
//测试,左查询
select ww.bage || '' || ww.age as age from
(select w.*,m.age from
(select s.*,t.age as bage from aaa s left join bbb t on s.id = t.id
order by s.id) w left join ccc m on w.id = m.id order by w.id) ww
//测试2,合并,配合左查询将空值字段合并
select ww.bage || '' || ww.age as age from
(select w.*,m.age from
(select s.*,t.age as bage from aaa s left join bbb t on s.id = t.id
order by s.id) w left join ccc m on w.id = m.id order by w.id) ww
//测试3,将多个字段的一条记录拆分成多条记录
select h.* from
(with t1 as
(
select t.c1 c1,t.c2 c2 from xxx t
)
select distinct regexp_substr(c1, '[^|]+',1,level) c1,
regexp_substr(c2, '[^|]+',1,level) c2
from t1
connect by level <= length(c1) - length(replace(c1,'|','')) + 1
order by c1) h
//测试4 拆分字符串
select substr(x.c1,1,instr(x.c1,',')-1) as xindex,
substr(x.c1,instr(x.c1,',')+1) as xcontent
from xxx x
//教科研成果数量
select wmsys.wm_concat(gg.dic_name || ',' || hh.dic_num) as scresult from
(select t.dic_name,t.dic_code from T_SYS_DIC t where
t.parent_id = (select id from T_SYS_DIC where dic_code = 'CGLX')order by t.dic_code) gg,
(select
substr(h.scresult,1,instr(h.scresult,',')-1) as dic_code,
substr(h.scresult,instr(h.scresult,',')+1) as dic_num
from
(
with t1 as
(
select mm.scresult scresult from (select
a.ax || '|' ||b.bx || '|' ||c.cx || '|' ||d.dx || '|' ||e.ex || '|' ||f.fx || '|' ||g.gx || '|' ||h.hx || '|' ||i.ix
as scresult
from
(select result_type || ',' || count(id) as ax from
T_JW_TEACHINGRESULT_PROJECT group by result_type) a,
(select result_type || ',' || count(id) as bx from
T_JW_TEACHINGRESULT_STANDARD group by result_type) b,
(select result_type || ',' || count(id) as cx from
T_JW_TEACHINGRESULT_MEDICINE group by result_type) c,
(select result_type || ',' || count(id) as dx from
T_JW_TEACHINGRESULT_PAPER group by result_type) d,
(select result_type || ',' || count(id) as ex from
T_JW_TEACHINGRESULT_ART group by result_type) e,
(select result_type || ',' || count(id) as fx from
T_JW_TEACHINGRESULT_GUIDE group by result_type) f,
(select result_type || ',' || count(id) as gx from
T_JW_TEACHINGRESULT_WRITING group by result_type) g,
(select result_type || ',' || count(id) as hx from
T_JW_TEACHINGRESULT_PATENT group by result_type) h,
(select result_type || ',' || count(id) as ix from
T_JW_TEACHINGRESULT_REPORT group by result_type) i ) mm
)
select distinct regexp_substr(scresult, '[^|]+',1,level) scresult
from t1
connect by level <= length(scresult) - length(replace(scresult,'|','')) + 1
order by scresult
) h
) hh
where gg.dic_code = hh.dic_code
//各级学生人数
select wmsys.wm_concat(hh.stunum) as stunum from
(select mm.dic_name || ',' || (case when to_char(mm.name) is null then '0,0' else to_char(mm.name) end) stunum from
(select m.dic_name,h.name from
(select t.dic_name,t.dic_code from T_SYS_DIC t where t.parent_id =
(select id from T_SYS_DIC where dic_code = 'ZJBJLX')) m
left join
(select s.class_type,sum(case when t.sex = '1' then 1 else 0 end) || ',' ||
sum(case when t.sex = '2' then 1 else 0 end) as name
from T_XG_STUDENT t,T_XG_CLASS s
where t.class_name = s.class_name
group by s.class_type
) h
on m.dic_code = h.class_type) mm) hh
//实习就业情况
select wmsys.wm_concat(q.nnum) as sxjy from
(select t.dept_name || ',' ||count(s.student_id) || ',' ||
count(u.student_id) as nnum
from T_SYS_DEPT t
left join
T_XG_INTERNSHIP_STUDENT s
on s.departments_id = t.dept_id
left join
T_XG_EMPLOYMENT_STUDENT u
on u.departments_id = t.dept_id
where t.dept_type = 8
group by t.dept_name) q
//评教日期,数量
select wmsys.wm_concat(oo.nnum) as alltask from
(select nn.adate || ',' ||
(case when to_char(ll.nnum0) is null then '0' else to_char
(ll.nnum0) end) || ',' ||
(case when to_char(yy.nnum1) is null then '0' else to_char
(yy.nnum1) end) || ',' ||
(case when to_char(uu.nnum2) is null then '0' else to_char
(uu.nnum2) end) || ',' ||
(case when to_char(ii.nnum3) is null then '0' else to_char
(ii.nnum3) end) as nnum
from
(with t1 as
(
select h.ddate adate from
(select s.ddate || '01,'
|| s.ddate || '02,' || s.ddate || '03,' || s.ddate || '04,'
|| s.ddate || '05,' || s.ddate || '06,' || s.ddate || '07,'
|| s.ddate || '08,' || s.ddate || '09,' || s.ddate || '10,'
|| s.ddate || '11,' || s.ddate || '12'
as ddate from
(select to_char(sysdate,'YYYY-') as ddate from dual) s) h
)
select distinct regexp_substr(adate, '[^,]+',1,level) adate
from t1
connect by level <= length(adate) - length(replace(adate,',','')) + 1
order by adate) nn
left join
(select s.ddate,sum(s.nnum) as nnum0 from
(select to_char(t.start_time,'YYYY-MM') as ddate, count(t.task_id) as nnum from T_KH_EVALUATION_TASK t
where t.task_type = 0
group by t.start_time
order by t.start_time) s
group by s.ddate
order by s.ddate) ll
on nn.adate = ll.ddate
left join
(select s.ddate,sum(s.nnum) as nnum1 from
(select to_char(t.start_time,'YYYY-MM') as ddate, count(t.task_id) as nnum from T_KH_EVALUATION_TASK t
where t.task_type = 1
group by t.start_time
order by t.start_time) s
group by s.ddate
order by s.ddate) yy
on nn.adate = yy.ddate
left join
(select s.ddate,sum(s.nnum) as nnum2 from
(select to_char(t.start_time,'YYYY-MM') as ddate, count(t.task_id) as nnum from T_KH_EVALUATION_TASK t
where t.task_type = 2
group by t.start_time
order by t.start_time) s
group by s.ddate
order by s.ddate) uu
on nn.adate = uu.ddate
left join
(select s.ddate,sum(s.nnum) as nnum3 from
(select to_char(t.start_time,'YYYY-MM') as ddate, count(t.task_id) as nnum from T_KH_EVALUATION_TASK t
where t.task_type = 3
group by t.start_time
order by t.start_time) s
group by s.ddate
order by s.ddate) ii
on nn.adate = ii.ddate
order by nn.adate) oo
//评教名称
select wmsys.wm_concat(h.dic_name) as taskname from
(select t.dic_name from T_SYS_DIC t
where t.parent_id = (select id from T_SYS_DIC where dic_code = 'JSPJRWLX')
order by t.dic_code) h
//学生教师综合成绩统计
select
(case when to_char(gg.nnum) is null then '0' else to_char
(gg.nnum) end) || ',' ||
(case when to_char(jj.nnum) is null then '0' else to_char
(jj.nnum) end) as score
from
(select h.score from
(with t1 as
(
select t.score score from
(select '100,90,80,70,60,10' as score from dual) t
)
select distinct to_number(regexp_substr(score, '[^,]+',1,level)) score
from t1
connect by level <= length(score) - length(replace(score,',','')) + 1
order by score desc) h) ff
left join
(select
(case when t.score = 100 then 100 else
case when t.score >=90 and t.score < 100 then 90 else
case when t.score >=80 and t.score <90 then 80 else
case when t.score >=70 and t.score <80 then 70 else
case when t.score >=60 and t.score <70 then 60 else
10
end end end end end) as score,
count(t.score) as nnum
from T_KH_EVALUATION_COMPLEX_TASK t
where t.test_flag = 1 and t.evaluation_type = 1
group by t.score
order by t.score desc) gg
on ff.score = gg.score
left join
(select
(case when t.score = 100 then 100 else
case when t.score >=90 and t.score < 100 then 90 else
case when t.score >=80 and t.score <90 then 80 else
case when t.score >=70 and t.score <80 then 70 else
case when t.score >=60 and t.score <70 then 60 else
10
end end end end end) as score,
count(t.score) as nnum
from T_KH_EVALUATION_COMPLEX_TASK t
where t.test_flag = 1 and t.evaluation_type = 1
group by t.score
order by t.score desc) jj
on ff.score = jj.score
order by ff.score desc
//校产类别
select wmsys.wm_concat(n.assets) as assets from
(select
t.dic_name || ',' || sum(s.assets_num_all) as assets from T_SYS_USERDIC t,T_ZW_ASSETS s
where t.dic_type = 'XCLB' and t.dic_code = s.assets_type
group by t.dic_name) n
//校产状态
select wmsys.wm_concat(h.nnum) as assetsstatus from
(select n.dic_name || ',' || sum(n.nnum) as nnum from
(select t.dic_name,t.dic_code,(case when m.nnum is null then 0 else m.nnum end) as nnum
from T_SYS_DIC t
left join
(select assets_num as nnum,status from T_ZW_ASSETS_DETAILS) m
on m.status = t.dic_code
where t.parent_id =
(select id from T_SYS_DIC where dic_code = 'XCZT')) n
group by n.dic_name) h
//部门维修数量
select wmsys.wm_concat(m.repairs) as repairs from
(select t.dept_name || ',' || count(s.repair_id) as repairs from T_SYS_DEPT t,T_ZW_REPAIR s
where s.dept = t.dept_id
group by t.dept_name) m
//党员类型数量
select wmsys.wm_concat(b.party) party from
(select n.party from
(select h.dic_code,h.dic_name || ',' || count(m.member_roles) as party from
(select t.dic_name,t.dic_code from T_SYS_DIC t
where t.parent_id = (select id from T_SYS_DIC where dic_code = 'DYJS')) h
left join
(select * from T_DT_PARTYMEMBERS where person_type = 0) m
on h.dic_code = m.member_roles
group by h.dic_name,h.dic_code) n
order by n.dic_code) b
//各部门党员数量
select wmsys.wm_concat(hh.nnum) as partydept from
(select h.dept_name || ',' || (case when j.nnum is null then 0 else j.nnum end) as nnum from
(select t.dept_name,t.dept_id from T_SYS_DEPT t where t.parent_dept_id = 001) h
left join
(select dept_id,count(person_id) as nnum from
T_DT_PARTYMEMBERS where member_roles = 4 group by dept_id) j
on h.dept_id = j.dept_id) hh
//教师个人空间
select wmsys.wm_concat(vv.jsinfo) as jsinfo from
(select kk.mmonth || ',' ||
(case when m.jsinfo is null then '0,0,0' else m.jsinfo end) as jsinfo from
(select hhh.mmonth,hhh.nnum from
(select hh.mmonth,to_number(replace(hh.mmonth,'月','')) as nnum from
(select h.* from
(with t1 as
(
select t.mmonth mmonth from (select '1月|2月|3月|4月|5月|6月|7月|8月|9月|10月|11月|12月' as mmonth from dual) t
)
select distinct regexp_substr(mmonth, '[^|]+',1,level) mmonth
from t1
connect by level <= length(mmonth) - length(replace(mmonth,'|','')) + 1
order by mmonth) h) hh) hhh order by hhh.nnum) kk
left join
(select t.year|| ',' || t.basic_frequency || ',' ||t.no_sign_in_frequency
as jsinfo,t.month || '月' as nnum
from T_KQ_INFO t where t.id = #{person} and t.valid_flag = '1') m
on m.nnum = kk.mmonth
order by kk.nnum) vv
//Notice.java DeptDao.xml EmpChangeLogController.java DeptController.java listNotice.jsp