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

问题描述

我的SQL查询没什么问题.我想获取我的CUSTOMERS表ID，名称，姓氏以及他们在我创建的商店中花费的所有资金.

I have little problem with my SQL query.I want to get my CUSTOMERS table id, name, surname, and all money they spent on my shop i've created.

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

I have to say, that i have to use GroupBy clause, because i have this in my school project requirements. And this is my base diagram, if it could help you.http://i61.tinypic.com/2d1okut.jpg

推荐答案

您可以在 ORACLE文档

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

This means that if you only group by o.CUSTOMER_ID, all the other fields in the select list must be aggregate functions (like COUNT, MAX, etc.).

如果字段在每个组中重复值(作为名称和姓氏)，则应将其包括在GORUP BY子句中.

In the case of fields that repeat values in each group (as name and surname) you should include them in the GORUP BY clause.

要包括花费的总金额，您可以添加带有产品"的另一个LEFT JOIN并选择SUM(op.amount*p.price)而不使用子查询.

To include the sum of money spent, you could add another LEFT JOIN with PRODUCTS and select SUM(op.amount*p.price) without a subquery.

那是

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;

请务必始终定义查询的排序顺序，否则它将是未定义的.

Remember always to define the sort order of your queries, otherwise it will be undefined.

这篇关于SQL不是带有OracleSQL和InnerQuery错误的GROUP BY表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！