对questionTable表进行查询

select # 对L_DEALID字段进行分组，如果是1则返回1008611，否则返回L_DEALID 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) + 1)) / 7) + 1 END ) ) ) <= 3 group by rollup(a.L_DEALID)

对questionTable表进行查询

(select DECODE(GROUPING(a.L_DEALID), 1, 1008611, a.L_DEALID) delearId, count(1) numbers from questionTable a where a.L_ANSWERTYPE = 2 group by rollup(a.L_DEALID) ) h,

对questionTable表进行查询

(select DECODE(GROUPING(a.L_DEALID), 1, 1008611, a.L_DEALID) delearId, count(1) numbers from questionTable a where a.L_ANSWERTYPE in (1, 7) group by rollup(a.L_DEALID) ) i,

对questionTable表进行查询

(select DECODE(GROUPING(a.L_DEALID), 1, 1008611, a.L_DEALID) delearId, count(1) numbers from questionTable a where a.L_ANSWERTYPE in (4, 5) group by rollup(a.L_DEALID) ) j,

对questionTable表进行查询

(select DECODE(GROUPING(L_DEALID), 1, 1008611, L_DEALID) delearId, count(1) numbers from ( select * from questionTable a where regexp_like(a.VC_TASKVERSION || ',', '([a-zA-Z]+)+\d{8}\d+,') ) group by rollup(L_DEALID) ) k,

对questionTable表进行查询

(select DECODE(GROUPING(a.L_DEALID), 1, 1008611, a.L_DEALID) delearId, count(1) numbers from questionTable a where a.D_REALPUBLISHDATE < a.D_PLANPUBLISHDATE group by rollup(a.L_DEALID) ) l,

对questionTable表进行查询

(select DECODE(GROUPING(a.L_DEALID), 1, 1008611, a.L_DEALID) delearId, count(1) numbers from questionTable a where a.D_REALPUBLISHDATE > a.D_PLANPUBLISHDATE group by rollup(a.L_DEALID) ) m,

对questionTable表进行查询

(select DECODE(GROUPING(L_DEALID), 1, 1008611, L_DEALID) delearId, count(1) numbers from questionTable where ( trunc(D_REALPUBLISHDATE - D_PLANPUBLISHDATE) - ( ( case WHEN (8 - to_number(to_char(D_PLANPUBLISHDATE, 'D'))) > trunc(D_REALPUBLISHDATE - D_PLANPUBLISHDATE) + 1 THEN 0 ELSE trunc((trunc(D_REALPUBLISHDATE - D_PLANPUBLISHDATE) - (8 - to_number(to_char(D_PLANPUBLISHDATE, 'D')))) / 7) + 1 END ) + ( case WHEN mod(8 - to_char(D_PLANPUBLISHDATE, 'D'), 7) > trunc(D_REALPUBLISHDATE - D_PLANPUBLISHDATE) - 1 THEN 0 ELSE trunc((trunc(D_REALPUBLISHDATE - D_PLANPUBLISHDATE) - (mod(8 - to_char(D_PLANPUBLISHDATE, 'D'), 7) + 1)) / 7) + 1 END ) ) ) > 0 and ( trunc(D_REALPUBLISHDATE - D_PLANPUBLISHDATE) - ( ( case WHEN (8 - to_number(to_char(D_PLANPUBLISHDATE, 'D'))) > trunc(D_REALPUBLISHDATE - D_PLANPUBLISHDATE) + 1 THEN 0 ELSE trunc((trunc(D_REALPUBLISHDATE - D_PLANPUBLISHDATE) - (8 - to_number(to_char(D_PLANPUBLISHDATE, 'D')))) / 7) + 1 END ) + ( case WHEN mod(8 - to_char(D_PLANPUBLISHDATE, 'D'), 7) > trunc(D_REALPUBLISHDATE - D_PLANPUBLISHDATE) - 1 THEN 0 ELSE trunc((trunc(D_REALPUBLISHDATE - D_PLANPUBLISHDATE) - (mod(8 - to_char(D_PLANPUBLISHDATE, 'D'), 7) + 1)) / 7) + 1 END ) ) ) <= 14 group by rollup(L_DEALID) ) n,

对questionTable表进行查询

(select DECODE(GROUPING(L_DEALID), 1, 1008611, L_DEALID) delearId, sum(( trunc(D_REALPUBLISHDATE - D_INPUTDATE) - ( ( case WHEN (8 - to_number(to_char(D_INPUTDATE, 'D'))) > trunc(D_REALPUBLISHDATE - D_INPUTDATE) + 1 THEN 0 ELSE trunc((trunc(D_REALPUBLISHDATE - D_INPUTDATE) - (8 - to_number(to_char(D_INPUTDATE, 'D')))) / 7) + 1 END ) + ( case WHEN mod(8 - to_char(D_INPUTDATE, 'D'), 7) > trunc(D_REALPUBLISHDATE - D_INPUTDATE) - 1 THEN 0 ELSE trunc((trunc(D_REALPUBLISHDATE - D_INPUTDATE) - (mod(8 - to_char(D_INPUTDATE, 'D'), 7) + 1)) / 7) + 1 END ) ) ))/count(1) spanDay from questionTable group by rollup(L_DEALID) ) o

连接a、h、i、j、k、l、m、n、o表

where a.FL_ID = b.delearId(+) and a.FL_ID = c.delearId(+) and a.FL_ID = d.delearId(+) and a.FL_ID = e.delearId(+) and a.FL_ID = f.delearId(+) and a.FL_ID = g.delearId(+) and a.FL_ID = h.delearId(+) and a.FL_ID = i.delearId(+) and a.FL_ID = j.delearId(+) and a.FL_ID = k.delearId(+) and a.FL_ID = l.delearId(+) and a.FL_ID = m.delearId(+) and a.FL_ID = n.delearId(+) and a.FL_ID = o.delearId(+)

对结果进行筛选

and :userName || ',' like '%' || userId ||',%'

对结果进行排序

order by total desc nulls last