oracle SQL 错误：ORA-00979：不是 GROUP BY 表达式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19018300/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Error: ORA-00979: not a GROUP BY expression
提问by user25830
Can anyone please help me figure this out, I have 3 tables: Customer, Products and Products_ordered and I am trying to find customers who have ordered more than 1 product. Here is my query:
任何人都可以帮我解决这个问题，我有 3 个表：Customer、Products 和 Products_ordered，我试图找到订购了 1 个以上产品的客户。这是我的查询：
SELECT customer_id, product_id
FROM product_ordered
GROUP BY customer_id
HAVING COUNT (customer_id)>1;
I am getting this error: Error report: SQL Error: ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression"
我收到此错误：错误报告：SQL 错误：ORA-00979：不是 GROUP BY 表达式 00979. 00000 -“不是 GROUP BY 表达式”
Thanks for helping
谢谢你的帮助
回答by sunysen
try
尝试
select customer_id, product_id from product_ordered group by customer_id,product_id having count (customer_id)>1;
回答by Vulcronos
Try:
尝试：
SELECT customer_id
FROM product_ordered
GROUP BY customer_id
HAVING COUNT (customer_id)>1;
The issue is that product_id is not part of the group by. If you do a group by, you can only select columns in the group by or use an aggregate function. That query will return the customer_id's that occur more then once. I don't know your table structure, but if you want more data then just the id let us know what sql version you are using, SQL Sever, MYSQL, or Oracle, and I can try to write something with windowing functions.
问题是 product_id 不是 group by 的一部分。如果做group by，只能选择group by中的列或者使用聚合函数。该查询将返回多次出现的 customer_id。我不知道你的表结构，但是如果你想要更多的数据，那么只要 id 让我们知道你使用的是什么 sql 版本，SQL Sever、MYSQL 或 Oracle，我可以尝试用窗口函数写一些东西。
回答by chrlsuk
Don't you really want to select Customers who ordered more than one product?
您真的不想选择订购了不止一种产品的客户吗？
More than one order line, or more than one product, or more than one unique product?
多于一个订单行，或多于一种产品，或多于一种独特的产品？
If you run as an inline query
如果您作为内联查询运行
(select customer_id from product_ordered group by customer_id having count (customer_id) > 1)
(select customer_id from product_ordered group by customer_id having count (customer_id) > 1)
You will see all customers who placed more than one order line. But there could be multiple lines in an order, or multiple orders of one line, yada yada...
您将看到所有下过一个订单行的客户。但是一个订单中可能有多行，或者一行中有多个订单，yada yada...
Try select customer_id from product_ordered group by customer_id having count(distinct product_id)>1which will let you actuallysee customers who bought more than one unique product.
试试select customer_id from product_ordered group by customer_id having count(distinct product_id)>1这会让您真正看到购买了不止一种独特产品的客户。
