Oracle SQL查询部分包含所需结果

Question

我的要求是显示国家名称、发票总数及其平均金额。此外，我只需要返回那些平均发票金额大于所有发票平均发票金额的国家。

Oracle数据库查询

SELECT cntry.NAME, 
COUNT(inv.NUMBER), 
AVG(inv.TOTAL_PRICE)
FROM   COUNTRY cntry JOIN
CITY ct ON ct.COUNTRY_ID = cntry.ID JOIN
CUSTOMER cst ON cst.CITY_ID = ct.ID JOIN
INVOICE inv ON inv.CUSTOMER_ID = cst.ID
GROUP BY cntry.NAME,
inv.NUMBER,
inv.TOTAL_PRICE
HAVING AVG(inv.TOTAL_PRICE) > (SELECT AVG(TOTAL_PRICE)
FROM INVOICE);

结果：奥地利1 9500

预期：奥地利2 4825

架构

国家

ID(INT)(PK) | NAME(VARCHAR)

城市

ID(INT)(PK) | NAME(VARCHAR) | POSTAL_CODE(VARCHAR) | COUNTRY_ID(INT)(FK)

客户

ID(INT)(PK) | NAME(VARCHAR) | CITY_ID(INT)(FK) | ADDRS(VARCHAR) | POC(VARCHAR) | EMAIL(VARCHAR) | IS_ACTV(INT)(0/1)

发票

ID(INT)(PK) | NUMBER(VARCHAR) | CUSTOMER_ID(INT)(FK) | USER_ACC_ID(INT) | TOTAL_PRICE(INT)

Answer 1

由于没有样本数据，我们无法真正判断这是否是：

预期：奥地利2 4825

是否为真。

---

无论如何：将GROUP BY子句更改为

GROUP BY cntry.NAME

(即从中移除另外两列(有什么好处吗？

Answer 2

`SELECT C.COUNTRY_NAME,COUNT(I.INVOICE_NUMBER),AVG(I.TOTAL_PRICE) AS AVERAGE 
FROM COUNTRY AS C JOIN CITY AS CS ON C.ID=CS.COUNTRY_ID
JOIN CUSTOMER AS CUS ON CUS.CITY_ID=CS.ID
JOIN INVOICE AS I ON I.CUSTOMER_ID=CUS.ID
GROUP BY C.COUNTRY_NAME,C.ID
HAVING AVERAGE>(SELECT AVG(TOTAL_PRICE) FROM INVOICE`

Answer 3

将GROUP BY子句更改为

GROUP BY cntry.NAME , cntry.ID

Answer 4

按列修复组。只保留cntry.name.

它会起作用的。

这是一个棘手的问题。