这是表结构

供体(供体ID,名称)
捐赠(ID,捐赠者ID,金额)

这是我的查询:

SELECT donor.name, SUM(donation.amount) "Total"
FROM donor, donation
WHERE donor.donorID = donation.donorID
AND SUM(donation.amount) > 1000;
GROUP BY donor.name
ORDER BY SUM(donation.amount) DESC;


这是错误:


ORA-00934:此处不允许使用组功能
00934. 00000-“此处不允许使用组功能”


当我删除sum(donation)我如何正确集成它?请帮忙。

最佳答案

您不能将聚合函数放在where子句中。您需要一个having子句:

SELECT donor.name, SUM(donation.amount) "Total"
FROM donor, donation
WHERE donor.donorID = donation.donorID
GROUP BY donor.name
HAVING SUM(donation.amount) > 1000
ORDER BY SUM(donation.amount) DESC;


但是,您应该学习使用正确的join语法和表别名:

SELECT d.name, SUM(dn.amount) as "Total"
FROM donor d JOIN
     donation dn
     ON d.donorID = dn.donorID
GROUP BY d.name
HAVING SUM(dn.amount) > 1000
ORDER BY SUM(dn.amount) DESC;

10-08 09:03