使用with语句创建questionTable和userTable两个临时表

with questionTable as ( select * from T_QUESTIONLIST a where a.VC_SYSTEMNO = :systemNo and a.D_DEALDATE between to_date(:periodTimeStart, 'yyyy-MM-dd HH24:mi:ss') and to_date(:periodTimeEnd, 'yyyy-MM-dd HH24:mi:ss') ), userTable as ( # 从TC_USER、TC_DICCHILD表中筛选符合条件的数据 SELECT A.FL_ID userId, A.FC_USERNAME userName, C.FC_VALUECOMMENT department, D.FC_VALUECOMMENT team, # 根据部门名称设置对应的系统编号 CASE WHEN INSTR(C.FC_VALUECOMMENT, '投资') = 1 THEN '04' WHEN INSTR(C.FC_VALUECOMMENT, '估值') = 1 THEN '01' WHEN INSTR(C.FC_VALUECOMMENT, 'TA') = 1 THEN '06' WHEN INSTR(C.FC_VALUECOMMENT, '测试') = 1 THEN '02' ELSE '05' END AS systemNo FROM TC_USER A, (SELECT * FROM TC_DICCHILD WHERE FC_DICNO = 'T005') C, (SELECT * FROM TC_DICCHILD WHERE FC_DICNO = 'T007') D WHERE C.FC_DICVALUE(+) = A.VC_DEPARTMENT AND D.FC_DICVALUE(+) = A.VC_TEAM and nvl(A.VC_TEAM, ' ') != '0388' and nvl(A.VC_DEPARTMENT, ' ') != '14' and a.VC_MANAGERNO = '0000' # 将指定的数据添加到结果集中 union all select 1008611 userId, '汇总' userName, :targetType department, :targetType team, :systemNo systemNo from dual )

查询结果集

select * from ( # 筛选符合条件的用户数据 select a.FL_ID userId, a.FC_USERNAME userName, b.numbers total, c.numbers effective, d.numbers inner, e.numbers customer, f.numbers improved, ROUND(g.numbers/c.numbers * 100, 2) response, ROUND(h.numbers/c.numbers * 100, 2) realize, ROUND(i.numbers/c.numbers * 100, 2) planUpdate, ROUND(j.numbers/c.numbers * 100, 2) noUpdate, ROUND(k.numbers/c.numbers * 100, 2) patch, ROUND(l.numbers/c.numbers * 100, 2) promptly, ROUND(m.numbers/c.numbers * 100, 2) delay, ROUND(n.numbers/c.numbers * 100, 2) delayFourTeen, o.spanDay from ( select userId FL_ID, userName FC_USERNAME from userTable where systemNo = :systemNo and department like '%' || :targetType ||'%' ) a, # 统计问题总数 (select DECODE(GROUPING(a.L_DEALID), 1, 1008611, a.L_DEALID) delearId, count(1) numbers from questionTable a group by rollup(a.L_DEALID) ) b, # 统计有效问题数 (select DECODE(GROUPING(a.L_DEALID), 1, 1008611, a.L_DEALID) delearId, count(1) numbers from questionTable a where a.L_ANSWERTYPE in (1, 2, 4, 5, 7) group by rollup(a.L_DEALID) ) c, # 统计内部问题数 (select DECODE(GROUPING(a.L_DEALID), 1, 1008611, a.L_DEALID) delearId, count(1) numbers from questionTable a where a.L_TYPE in (1, 5) group by rollup(a.L_DEALID) ) d, # 统计客户问题数 (select DECODE(GROUPING(a.L_DEALID), 1, 1008611, a.L_DEALID) delearId, count(1) numbers from questionTable a where a.L_TYPE in (2, 3, 4) group by rollup(a.L_DEALID) ) e, # 统计已改进问题数 (select DECODE(GROUPING(a.L_DEALID), 1, 1008611, a.L_DEALID) delearId, count(1) numbers from questionTable a, T_QUESTADDHDXY c where a.L_QUESTIONID = c.L_QUESTIONID and c.L_QUESTTYPE in (2, 3) group by rollup(a.L_DEALID) ) f, # 统计平均响应时间 (select DECODE(GROUPING(a.L_DEALID), 1, 1008611, a.L_DEALID) delearId, count(1) numbers from questionTable a where (trunc(a.D_RETURNDATE - a.D_INPUTDATE) - ((case WHEN (8 - to_number(to_char(a.D_INPUTDATE, 'D'))) > trunc(a.D_RETURNDATE - a.D_INPUTDATE) + 1 THEN 0 ELSE trunc((trunc(a.D_RETURNDATE - a.D_INPUTDATE) - (8 - to_number(to_char(a.D_INPUTDATE, 'D')))) / 7) + 1 END) + (case WHEN mod(8 - to_char(a.D_INPUTDATE, 'D'), 7) > trunc(a.D_RETURNDATE - a.D_INPUTDATE) - 1 THEN 0 ELSE trunc((trunc(a.D_RETURNDATE - a.D_INPUTDATE) - (mod(8 - to_char(a.D_INPUTDATE, 'D'), 7)