oracle 函数 rollup

点击查看代码

 
 select 
 tt.departmentId,nvl(tt.departmentName,'合计') as departmentName,
 sum(tt.totalTutorNum),
 sum(tt.recruidTutorNum),
 sum(tt.inSchoolStuNum),
sum( tt.stuAndTutorRate),
 sum(tt.inTutorNum),
 sum(tt.outTutorNum),
 sum(tt.normalTutorNum),
 sum(tt.hightTutorNum)
 from (
 select dw.dw_id as departmentId, dw.dwmc as departmentName
          , nvl(xyds.nums,0) as totalTutorNum
          , nvl(zsds.nums,0) as recruidTutorNum
          , nvl(zjs.nums,0) as inSchoolStuNum
         --师生比（在籍生/招生导师）
          ,case when zsds.nums =0 or zsds.nums is null then 0 else ROUND(nvl(zjs.nums,0)/zsds.nums,2) end  as stuAndTutorRate
          , nvl(xnds.nums,0) as inTutorNum
          , nvl(xwds.nums,0) as outTutorNum
          , nvl(sds.nums,0) as normalTutorNum
          , nvl(bds.nums,0) as hightTutorNum

          from (
             select * from ly_yjs_hxsj.t_Ggzy_Dwxx dw where dw.sfzsdw ='1'
               union
               select dw.*
               from ly_yjs_hxsj.t_Ggzy_Dwxx dw
               left join ly_yjs_hxsj.t_szgl_dsxx  t   on dw.dw_id = t.xsm
               where (dw.sfzsdw !='1' or dw.sfzsdw is null) and  instr(dslx,'2')>0
          ) dw
         -- <!--现有导师数-->
          left join (
               select xsm, count(jgh) as nums from ly_yjs_hxsj.t_szgl_dsxx group by xsm
          ) xyds on xyds.xsm = dw.dw_id
       --<!--招生导师数-->
          left join (
               select xsm, count(jgh) as nums from ly_yjs_hxsj.t_szgl_dsxx where sftz = '1' group by xsm
          ) zsds on zsds.xsm = dw.dw_id
       -- <!--在籍生数-->
          left join (
               select xy, count(xs_id) as nums from ly_yjs_hxsj.t_xsgl_xsxx_xjxx where sfzx ='1'  and xy !='000000'  group by xy
          ) zjs on zjs.xy = dw.dw_id
      --  <!--校内导师数-->
          left join (
               select xsm, count(jgh) as nums from ly_yjs_hxsj.t_szgl_dsxx where dssx ='1' group by xsm
          ) xnds on xnds.xsm = dw.dw_id
       -- <!--校外导师数-->
          left join (
               select xsm, count(jgh) as nums from ly_yjs_hxsj.t_szgl_dsxx where dssx ='0'  group by xsm
          ) xwds on xwds.xsm = dw.dw_id
       -- <!--硕导数-->
          left join (
               select xsm, count(jgh) as nums from ly_yjs_hxsj.t_szgl_dsxx where instr(dslx,'1')>0  group by xsm
          ) sds on sds.xsm = dw.dw_id
       -- <!--博导数-->
          left join (
               select xsm, count(jgh) as nums from ly_yjs_hxsj.t_szgl_dsxx where instr(dslx,'2')>0  group by xsm
          ) bds on bds.xsm = dw.dw_id
        --<!--where dw.SFZSDW ='1'-->
          order by dw.dw_id
          
          ) tt 
          group by rollup((tt.departmentId,tt.departmentName))