GROUP BY/SQL中的聚合函数混淆 [英] GROUP BY / aggregate function confusion in SQL

问题描述

我需要一些帮助来弄清一些东西，我知道这是一个非常简单的问题，但这在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子句中列出所有6列(按名称-您不能在GROUP BY或ORDER 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.)

这篇关于GROUP BY/SQL中的聚合函数混淆的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！