SQL 不是带有 OracleSQL 和 InnerQuery 错误的 GROUP BY 表达式

Question

我的 SQL 查询没有什么问题。我想要获取我的 CUSTOMERS 表 ID、姓名以及他们在我创建的商店上花费的所有资金。

SELECT o.CUSTOMER_ID AS "ID", c.name AS "Name", c.SURNAME AS "Surname",
       (SELECT op.AMOUNT * p.PRICE 
          FROM PRODUCTS p 
         WHERE p.id = op.PRODUCT_ID) AS "Money spent"
  FROM ORDERS o 
       LEFT JOIN CUSTOMERS c ON c.ID = o.CUSTOMER_ID 
       LEFT JOIN ORDERS_PRODUCTS op ON op.ORDER_ID = o.id
 GROUP BY o.CUSTOMER_ID;

我有这样的错误消息:

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
Error at Line: 1 Column: 31

我不得不说，我必须使用 GroupBy 子句，因为我的学校项目要求中有这个。这是我的基本图，如果它可以帮助你的话。 http://i61.tinypic.com/2d1okut.jpg

Answer 1

正如您在 ORACLE docs 中看到的那样

SelectItems in the SelectExpression with a GROUP BY clause must contain only aggregates or grouping columns.

这意味着，如果您仅按 o.CUSTOMER_ID 进行分组，则选择列表中的所有其他字段都必须是聚合函数(例如 COUNT、MAX 等)。

如果字段在每个组中重复值(如姓名)，您应该将它们包含在 GORUP BY 子句中。

要包含花费的金额，您可以添加另一个 LEFT JOIN 与 PRODUCTS 并选择不带子查询的 SUM(op.amount*p.price)。

那就是

SELECT o.CUSTOMER_ID AS "ID", c.name AS "Name", c.SURNAME AS "Surname",
       SUM(op.AMOUNT*p.PRICE) AS "Money spent"
  FROM ORDERS o 
       LEFT JOIN CUSTOMERS c ON c.ID = o.CUSTOMER_ID 
       LEFT JOIN ORDERS_PRODUCTS op ON op.ORDER_ID = o.id
       LEFT JOIN PRODUCTS p ON p.id = op.PRODUCT_ID
 GROUP BY o.CUSTOMER_ID, c.name AS "Name", c.SURNAME
 ORDER BY o.CUSTOMER_ID, c.name AS "Name", c.SURNAME;

记住始终定义查询的排序顺序，否则它将是未定义的。