Oracle两表联合查询group by的sum后,显示max,竟然花了1.6秒,请教如何优化。 数据也就20000行而已
发布网友
发布时间:2022-04-13 15:33
我来回答
共5个回答
热心网友
时间:2022-04-13 17:02
尽量减少查询结果集,连接查询的结果
SELECT C.CLIENTNO,C.CNAME,QUERY1.TAMOUNT
FROM CLIENT C,(SELECT CLIENTNO,SUM(AMOUNT) TAMOUNT
FROM PURCHASE GROUP BY CLIENTNO order by TAMMOUNT desc) QUERY1
WHERE C.CLIENTNO = QUERY1.CLIENTNO and Query1.ROWNUM=1
热心网友
时间:2022-04-13 18:20
select * from
(SELECT b.CLIENTNO,b.CNAME,SUM(a.AMOUNT) TAMOUNT
FROM PURCHASE a, CLIENT b
where a.CLIENTNO=b.CLIENTNO
GROUP BY a.CLIENTNO
order by TAMOUNT) aa
where rowNum=1
热心网友
时间:2022-04-13 19:55
SELECT C.CLIENTNO,C.CNAME,SUM(P.AMOUNT) TAMOUNT
FROM PURCHASE P,
CLIENT C
WHERE C.CLIENTNO=P.CLIENTNO
GROUP BY C.CLIENTNO,C.CNAME
HAVING SUM(P.AMOUNT) = MAX(SELECT SUM(AMOUNT)
FROM PURCHASE
GROUP BY CLIENTNO)
热心网友
时间:2022-04-13 21:46
解释计划发出来,看看。
热心网友
时间:2022-04-13 23:54
select MAX(fjname),MAX(name),MAX(addr),hm,sum(charge) from owe group by hm
没在GROUP BY 中的都要加集计函数
...显示max,竟然花了1.6秒,请教如何优化。 数据也就20000行而已_百度知...
尽量减少查询结果集,连接查询的结果 SELECT C.CLIENTNO,C.CNAME,QUERY1.TAMOUNT FROM CLIENT C,(SELECT CLIENTNO,SUM(AMOUNT) TAMOUNT FROM PURCHASE GROUP BY CLIENTNO order by TAMMOUNT desc) QUERY1 WHERE C.CLIENTNO = QUERY1.CLIENTNO and Query1.ROWNUM=1 ...