坟墓里的爱情 2016-10-31 02:28 采纳率: 0%
浏览 14238

mybatis 查询大量数据 效率低

下面是我的mapper文件,查询超级慢,请问有什么办法优化吗

 <select id="selectMedAlls" resultMap="BaseResultMap" >
with temp as
 (select FUN_GET_DEPT_NAME(a.nurse_cell_code) as nurse_cell_name,
         month_code,
         sum(a.out_num) as out_num, /*出院人数*/
         sum(a.bednum) as sjkfzcrs /*实际开放总床日数*/,
         round((sum(a.bednum) / 30), 1) as pjkfcws /*平均开发床位数*/,
         sum(a.end_num) as sjzyzcws /*实际占用总床日数*/ /*'期末人数'*/
    from nurse_day_report a
   where a.month_code between '201501' and '201511'
   group by a.nurse_cell_code, month_code
   order by a.nurse_cell_code),
   data as (
select 'thisM' as time ,
nvl(max((select count(1) as aa
             from fin_opr_register t1
            WHERE t1.dept_code not in ('0019', '0277', '0278') /*挂号主表.科室号 急诊科 急诊外科 急诊产科*/
              and t1.valid_flag = '1'
              and t1.reglevl_name <![CDATA[<>]]> ' '
              and to_char(t1.oper_date, 'yyyymm') = month_code)),0) as regist, /*门诊人次:期内科室号不属于'0019', '0277', '0278'的有效挂号人数*/
       nvl((sum(temp.out_num)),0) as outer, /*期内出院人数总计:0 治愈1 好转 2 未愈3 死亡 4 其他'*/
       nvl(sum(sjzyzcws),0) as sjzyzcrs, /*实际占用总床日数,*/
       nvl(to_number(nvl(decode(sum(pjkfcws), 0, 0, round(sum(out_num) / sum(pjkfcws), 1)),0)),0) as zzcs, /*周转次数,*/
       nvl(to_number(nvl(decode(sum(sjkfzcrs), 0, 0, round(sum(sjzyzcws) * 100 / sum(sjkfzcrs), 1)), 0)),0) as cwsyl /*床位使用率*/
  from temp
  where month_code='201511'

union all
select 'lastM' ,
nvl(max((select count(1) as aa
             from fin_opr_register t1
            WHERE t1.dept_code not in ('0019', '0277', '0278') /*挂号主表.科室号 急诊科 急诊外科 急诊产科*/
              and t1.valid_flag = '1'
              and t1.reglevl_name <![CDATA[<>]]> ' '
              and to_char(t1.oper_date, 'yyyymm') = month_code)),0) as regist, /*门诊人次:期内科室号不属于'0019', '0277', '0278'的有效挂号人数*/
       nvl((sum(temp.out_num)),0) as outer, /*期内出院人数总计:0 治愈1 好转 2 未愈3 死亡 4 其他'*/
       nvl(sum(sjzyzcws),0) as sjzyzcrs, /*实际占用总床日数,*/
       nvl(to_number(nvl(decode(sum(pjkfcws), 0, 0, round(sum(out_num) / sum(pjkfcws), 1)),0)),0) as zzcs, /*周转次数,*/
       nvl(to_number(nvl(decode(sum(sjkfzcrs), 0, 0, round(sum(sjzyzcws) * 100 / sum(sjkfzcrs), 1)), 0)),0) as cwsyl /*床位使用率*/
  from temp
  where month_code='201511'
  union all
  select 'thisY' ,
nvl(max((select count(1) as aa
             from fin_opr_register t1
            WHERE t1.dept_code not in ('0019', '0277', '0278') /*挂号主表.科室号 急诊科 急诊外科 急诊产科*/
              and t1.valid_flag = '1'
              and t1.reglevl_name <![CDATA[<>]]> ' '
              and to_char(t1.oper_date, 'yyyymm') = month_code)),0) as regist, /*门诊人次:期内科室号不属于'0019', '0277', '0278'的有效挂号人数*/
       nvl((sum(temp.out_num)),0) as outer, /*期内出院人数总计:0 治愈1 好转 2 未愈3 死亡 4 其他'*/
       nvl(sum(sjzyzcws),0) as sjzyzcrs, /*实际占用总床日数,*/
       nvl(to_number(nvl(decode(sum(pjkfcws), 0, 0, round(sum(out_num) / sum(pjkfcws), 1)),0)),0) as zzcs, /*周转次数,*/
       nvl(to_number(nvl(decode(sum(sjkfzcrs), 0, 0, round(sum(sjzyzcws) * 100 / sum(sjkfzcrs), 1)), 0)),0) as cwsyl /*床位使用率*/
  from temp
  where month_code between '201601' and '201511'
  union all
  select 'lastY' ,
nvl(max((select count(1) as aa
             from fin_opr_register t1
            WHERE t1.dept_code not in ('0019', '0277', '0278') /*挂号主表.科室号 急诊科 急诊外科 急诊产科*/
              and t1.valid_flag = '1'
              and t1.reglevl_name <![CDATA[<>]]> ' '
              and to_char(t1.oper_date, 'yyyymm') = month_code)),0) as regist, /*门诊人次:期内科室号不属于'0019', '0277', '0278'的有效挂号人数*/
       nvl((sum(temp.out_num)),0) as outer, /*期内出院人数总计:0 治愈1 好转 2 未愈3 死亡 4 其他'*/
       nvl(sum(sjzyzcws),0) as sjzyzcrs, /*实际占用总床日数,*/
       nvl(to_number(nvl(decode(sum(pjkfcws), 0, 0, round(sum(out_num) / sum(pjkfcws), 1)),0)),0) as zzcs, /*周转次数,*/
       nvl(to_number(nvl(decode(sum(sjkfzcrs), 0, 0, round(sum(sjzyzcws) * 100 / sum(sjkfzcrs), 1)), 0)),0) as cwsyl /*床位使用率*/
  from temp
  where  month_code between '201501' and '201511'
)
select 
sum(case when TIME = 'thisM' then regist else 0 end) as "thisM",
sum(case when TIME = 'lastM' then regist else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then regist else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then regist else 0 end)-sum(case when TIME = 'lastM' then regist else 0 end))*100/sum(case when TIME = 'lastM' then regist else 0 end),3))as lvM,
sum(case when TIME = 'thisY' then regist else 0 end) as "thisY",
sum(case when TIME = 'lastY' then regist else 0 end) as "lastY" ,
decode(sum(case when TIME = 'lastY' then regist else 0 end) ,0,0,round((sum(case when TIME = 'thisY' then regist else 0 end) -sum(case when TIME = 'lastY' then regist else 0 end) )*100/sum(case when TIME = 'lastY' then regist else 0 end)  ,1))as lvY
from data
union all
select 
sum(case when TIME = 'thisM' then outer else 0 end) as "thisM",
sum(case when TIME = 'lastM' then outer else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then outer else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then outer else 0 end)-sum(case when TIME = 'lastM' then outer else 0 end))*100/sum(case when TIME = 'lastM' then outer else 0 end),1)) as LvM,
sum(case when TIME = 'thisY' then outer else 0 end) as "thisY",
sum(case when TIME = 'lastY' then outer else 0 end) as "lastY",
decode(sum(case when TIME = 'lastY' then outer else 0 end),0,0,round((sum(case when TIME = 'thisY' then outer else 0 end)-sum(case when TIME = 'lastY' then outer else 0 end))*100/sum(case when TIME = 'lastY' then outer else 0 end),1) ) as LvY
from data

union all
select 
sum(case when TIME = 'thisM' then sjzyzcrs else 0 end) as "thisM",
sum(case when TIME = 'lastM' then sjzyzcrs else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then sjzyzcrs else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then sjzyzcrs else 0 end)-sum(case when TIME = 'lastM' then sjzyzcrs else 0 end))*100/sum(case when TIME = 'lastM' then sjzyzcrs else 0 end),1)) as LvM,
sum(case when TIME = 'thisY' then sjzyzcrs else 0 end) as "thisY",
sum(case when TIME = 'lastY' then sjzyzcrs else 0 end) as "lastY",
decode(sum(case when TIME = 'lastY' then sjzyzcrs else 0 end) ,0,0,round((sum(case when TIME = 'thisY' then sjzyzcrs else 0 end)-sum(case when TIME = 'lastY' then sjzyzcrs else 0 end) )*100/sum(case when TIME = 'lastY' then sjzyzcrs else 0 end)  ,1)) as LvY
 from data
union all
select 
sum(case when TIME = 'thisM' then zzcs else 0 end) as "thisM",
sum(case when TIME = 'lastM' then zzcs else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then zzcs else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then zzcs else 0 end)-sum(case when TIME = 'lastM' then zzcs else 0 end))*100/sum(case when TIME = 'lastM' then zzcs else 0 end),1))  as LvM,
sum(case when TIME = 'thisY' then zzcs else 0 end) as "thisY",
sum(case when TIME = 'lastY' then zzcs else 0 end) as "lastY",
decode(sum(case when TIME = 'lastY' then zzcs else 0 end),0,0,round((sum(case when TIME = 'thisY' then zzcs else 0 end)-sum(case when TIME = 'lastY' then zzcs else 0 end))*100/sum(case when TIME = 'lastY' then zzcs else 0 end) ,1)) as LvY
from data
union all
select 
sum(case when TIME = 'thisM' then cwsyl else 0 end) as "thisM",
sum(case when TIME = 'lastM' then cwsyl else 0 end) as "lastM",
decode(sum(case when TIME = 'lastM' then cwsyl else 0 end) ,0,0,round((sum(case when TIME = 'thisM' then cwsyl else 0 end)-sum(case when TIME = 'lastM' then cwsyl else 0 end))*100/sum(case when TIME = 'lastM' then cwsyl else 0 end),1)) as LvM,
sum(case when TIME = 'thisY' then cwsyl else 0 end) as "thisY",
sum(case when TIME = 'lastY' then cwsyl else 0 end) as "lastY",
decode(sum(case when TIME = 'lastY' then cwsyl else 0 end),0,0,round((sum(case when TIME = 'thisY' then cwsyl else 0 end)-sum(case when TIME = 'lastY' then cwsyl else 0 end))*100/sum(case when TIME = 'lastY' then cwsyl else 0 end) ,1)) as LvY
from data
  </select>
  • 写回答

3条回答 默认 最新

  • 坟墓里的爱情 2016-10-31 02:30
    关注

    查询出的结果是
    图片说明

    评论

报告相同问题?

悬赏问题

  • ¥15 vue2(标签-chrome|关键词-浏览器兼容)
  • ¥15 python网络流自动生成系统 医学领域
  • ¥15 sql查询仓库里都有什么
  • ¥15 代码的修改,添加和运行完善
  • ¥15 krpano-场景分组和自定义地图分组
  • ¥15 lammps Gpu加速出错
  • ¥15 关于PLUS模型中kapaa值的问题
  • ¥15 关于博途V17进行仿真时无法建立连接问题
  • ¥15 机器学习教材中的例题询问
  • ¥15 求.net core 几款免费的pdf编辑器