原sql:
select e.comment_id, e.evaluate_type, wmsys.wm_concat(evaluate_label) labels
from evaluating_label e
where exists(select *
from evaluation o
where o.order_id = '123' and e.comment_id = o.id)
group by e.comment_id, e.evaluate_type
效率极低,三万多条数据十多分钟都没查出来
替换listagg后
select e.comment_id,
e.evaluate_type,
listagg(e.EVALUATE_LABEL, ',') within
group (
order by null) labels
from evaluating_label e
where exists
(select *
from evaluation o
where o.order_id = '123' and e.comment_id = o.id)
group by e.comment_id, e.evaluate_type;
三秒内就出结果了。。。。
补充函数文档说明:
listagg()
String Aggregation Techniques
值得一提的是,WM_CONCAT在12c后的版本被删除掉了.