khbh的参数值过多时,直接查询数据会报错,拼接字符串过长。具体查询数据如下
查出多条数据:
维护查询出来的数据:
将维护好的数据拼接起来,查询结果为clob,常规取值即可:
select ' (4)部分预警课程分析\n'||rtrim(xmlagg(xmlelement(e,mc,'/n').extract('//text()') order by mc).GetClobVal(),'/n') mc from(
select kcmc||'总平均分'||pjf||'分,总优秀率为'||yxbl||'%,'||'总不及格率为'||bjgbl||'%。' mc from(
select distinct kcmc,to_char(pjf,'fm9999990.09') pjf,to_char(bjgbl,'fm9999990.09') bjgbl,to_char(yxbl,'fm9999990.09') yxbl from (
select kcmc,sum(ksrs) zrs,
ROUND(sum(pjf*ksrs)/sum(ksrs),2) pjf,
ROUND(sum(yxbl*ksrs)/sum(ksrs),2) yxbl,
ROUND(sum(bjgbl*ksrs)/sum(ksrs),2) bjgbl,
ROUND(sum(lhbl*ksrs)/sum(ksrs),2) lhbl,
ROUND(sum(jgbl*ksrs)/sum(ksrs),2) jgbl,
ROUND(sum(hkbl*ksrs)/sum(ksrs),2) hkbl,
ROUND(sum(mxbl*ksrs)/sum(ksrs),2) mxbl,
sum(yxsl) yxsl,
sum(lhsl) lhsl,
sum(bjgsl) bjgsl
from T_CJ_XSCJTJB2 t
where 1=1
and xnxq='2021-20221'
and khbh in ('21320034年4927','2021-2022165')
group by kcmc order by kcmc desc
) t order by kcmc desc
)where 1=1)