SQL 中的 GROUP BY/聚合函数混淆
我需要一些帮助来理顺一些问题，我知道这是一个非常简单的问题，但它在 SQL 中让我有点困惑.
I need a bit of help straightening out something, I know it's a very easy easy question but it's something that is slightly confusing me in SQL.
此 SQL 查询在 Oracle 中引发不是 GROUP BY 表达式"错误.我明白为什么，因为我知道，一旦我按元组的属性分组，我就无法再访问任何其他属性.
This SQL query throws a 'not a GROUP BY expression' error in Oracle. I understand why, as I know that once I group by an attribute of a tuple, I can no longer access any other attribute.
SELECT *
FROM order_details
GROUP BY order_no
但是这个确实有效
SELECT SUM(order_price)
FROM order_details
GROUP BY order_no
只是为了具体说明我对此的理解......假设每个订单的 order_details 中有多个元组，一旦我根据 order_no 对元组进行分组，我仍然可以访问每个单独元组的 order_price 属性组，但只使用聚合函数?
Just to concrete my understanding on this.... Assuming that there are multiple tuples in order_details for each order that is made, once I group the tuples according to order_no, I can still access the order_price attribute for each individual tuple in the group, but only using an aggregate function?
换句话说，在 SELECT 子句中使用聚合函数时，可以深入到组中查看隐藏"属性，而简单地使用SELECT order_no"会抛出错误?
In other words, aggregate functions when used in the SELECT clause are able to drill down into the group to see the 'hidden' attributes, where simply using 'SELECT order_no' will throw an error?
推荐答案
在标准 SQL(但不是 MySQL)中，当您使用 GROUP BY 时，您必须在 GROUP BY 子句中列出所有不是聚合的结果列.因此，如果 order_details 有 6 列，那么您必须在GROUP BY 子句.
In standard SQL (but not MySQL), when you use GROUP BY, you must list all the result columns that are not aggregates in the GROUP BY clause. So, if order_details has 6 columns, then you must list all 6 columns (by name - you can't use * in the GROUP BY or ORDER BY clauses) in the GROUP BY clause.
你也可以这样做:
SELECT order_no, SUM(order_price)
FROM order_details
GROUP BY order_no;
这会起作用，因为所有非聚合列都列在 GROUP BY 子句中.
That will work because all the non-aggregate columns are listed in the GROUP BY clause.
你可以这样做:
SELECT order_no, order_price, MAX(order_item)
FROM order_details
GROUP BY order_no, order_price;
这个查询没有真正意义(或者很可能没有意义)，但它会工作".它将列出每个单独的订单号和订单价格组合，并给出与该价格相关的最大订单项目(数量).如果订单中的所有商品都有不同的价格，您最终会得到每组一行.OTOH，如果订单中有几件商品的价格相同(比如每件 0.99 英镑)，那么它会将这些商品组合在一起并返回该价格的最大订单商品编号.(我假设该表在 (order_no, order_item) 上有一个主键，其中订单中的第一项具有 order_item = 1，第二项是 2，依此类推.)
This query isn't really meaningful (or most probably isn't meaningful), but it will 'work'. It will list each separate order number and order price combination, and will give the maximum order item (number) associated with that price. If all the items in an order have distinct prices, you'll end up with groups of one row each. OTOH, if there are several items in the order at the same price (say £0.99 each), then it will group those together and return the maximum order item number at that price. (I'm assuming the table has a primary key on (order_no, order_item) where the first item in the order has order_item = 1, the second item is 2, etc.)
相关文章