Oracle两表联合查询group by的sum后，显示max，竟然花了1.6秒，请教如何优化。 数据也就20000行而已

Question

这是我的语句，查询速度太慢了
SELECT C.CLIENTNO,C.CNAME,QUERY1.TAMOUNT
FROM CLIENT C,(SELECT CLIENTNO,SUM(AMOUNT) TAMOUNT
FROM PURCHASE
GROUP BY CLIENTNO
HAVING SUM(AMOUNT) >= ALL (SELECT SUM(AMOUNT)
FROM PURCHASE
GROUP BY CLIENTNO)) QUERY1
WHERE C.CLIENTNO = QUERY1.CLIENTNO
写一条SQL，找出购买总额最高的客户的ID,姓名和购买总额。
总额最高的用户不一定只有一个，有可能有多个。
具体的两张表和期待的显示结果如图。
ROWNUM=1 这样肯定不行，因为查询结果不一定是唯一的，有可能有多个并列最高的

Answer 1

尽量减少查询结果集，连接查询的结果
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

Answer 2

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追问

我不能保证最多的到底是一个还是多个，有可能有多个并列最多的

追答

无论多少个，反正需要在排序完后加一层取rowNum=1,也就是第一行，
坚决不能HAVING SUM(AMOUNT) >= ALL (SELECT SUM(AMOUNT)
FROM PURCHASE
GROUP BY CLIENTNO)
这样写，效率太低了。

如果比较繁琐的话，你可以写个存储过程来实现；
（这个问题用sql server 来实现非常简单，oracle麻烦了，也可能是我也不太了解呵）

追问

谢谢，你给了我启发，我又重写了一下，只需要0.06秒了

本回答被提问者采纳

Answer 3

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)

Answer 4

解释计划发出来，看看。

Answer 5

select MAX(fjname),MAX(name),MAX(addr),hm,sum(charge) from owe group by hm

没在GROUP BY 中的都要加集计函数