SQL 当我们在连接中使用聚合函数时如何使用 Group By 子句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6424092/
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
How to Use Group By clause when we use Aggregate function in the Joins?
提问by thevan
I want to join three tables and to calculate the Sum(Quantity) of the Table A. I tried something and I get the desired output. But still I have confusion based on aggregate function and Group By clause.
我想连接三个表并计算表 A 的总和（数量）。我尝试了一些方法，并得到了所需的输出。但是我仍然对聚合函数和 Group By 子句感到困惑。
While calculating the sum value by joining two or more tables, what are the columns we need to mention in the Group By clause and why do we need to give those columns?
在通过连接两个或多个表计算总和值时，我们需要在 Group By 子句中提及哪些列，为什么我们需要给出这些列？
For Example: Here is my table and the desired query.
例如：这是我的表和所需的查询。
TableA: ItemID, JobOrderID, CustomerID, DivisionID, Quantity
TableB: ItemID, ItemName, SpecificationID
TableC: SpecificationID, SpecificationName
TableD: DivisionID, DivisionName
TableE: JobOrderID, JobOrderNo.
TableF: CustomerID, CustomerName
I want to get the Sum(Quantity) based on ItemID, CustomerID, JobOrderIDand DivisionID.
我想根据获得的总和（数量）ItemID，CustomerID，JobOrderID和DivisionID。
I wrote the following query and it's working fine. But if I remove any column in the Group By clause, it doesn't give the desired result. Why? What does the Group By clause do here? How to specify the Group By clause when using Aggregate function? Here is my Query.
我写了以下查询，它工作正常。但是，如果我删除 Group By 子句中的任何列，则不会给出所需的结果。为什么？Group By 子句在这里有什么作用？使用聚合函数时如何指定 Group By 子句？这是我的查询。
SELECT
B.ItemName + ' - ' + C.SpecificationName AS 'ItemName',
SUM(A.Quantity) AS 'Quantity',
A.ItemID,
D.DivisionName,
F.CustomerName,
E.JobOrderNo,
A.DivisionID,
A.JobOrderID,
A.CustomerID
FROM
TableA A
INNER JOIN TableB B ON B.ItemID = A.ItemID
INNER JOIN TableC C ON C.SpecificationID = B.SpecificationID
INNER JOIN TableD D ON D.DivisionID = A.DivisionID
LEFT JOIN TableE E ON E.JobOrderID = A.JobOrderID
LEFT JOIN TableF F ON F.CustomerID = A.CustomerID
WHERE
A.ItemID = @ItemID
GROUP BY
A.ItemID,
A.JobOrderID,
A.DivisionID,
A.CustomerID,
D.DivisionName,
F.CustomerName,
E.JobOrderNo,
B.ItemName,
C.SpecificationName
Any one please give suggestion about the Group By Clause by considering this as an example.
任何人请以此为例提出有关“按条款分组”的建议。
回答by Piotr Auguscik
GROUP BYfor any unique combination of the specified columns does aggregation (like sum, min etc). If you don't specify some column name in the GROUP BYclause or in the aggregate function its unknown to the SQL engine which value it should return for that kind of column.
GROUP BY对于指定列的任何唯一组合进行聚合（如 sum、min 等）。如果您没有在GROUP BY子句或聚合函数中指定某个列名，SQL 引擎不知道它应该为那种列返回哪个值。
回答by Akram Shahda
GROUP BY (Transact-SQL)groups a selected set of rows into a set of summary rows by the values of one or more columns or expressions in SQL Server 2008 R2. One row is returned for each group. Aggregate functions in the SELECT clause list provide information about each group instead of individual rows.
GROUP BY (Transact-SQL)根据 SQL Server 2008 R2 中一个或多个列或表达式的值将一组选定的行分组为一组汇总行。每组返回一行。SELECT 子句列表中的聚合函数提供有关每个组而不是单个行的信息。
SELECT a.City, COUNT(bea.AddressID) AS EmployeeCount
FROM Person.BusinessEntityAddress AS bea
INNER JOIN Person.Address AS a
ON bea.AddressID = a.AddressID
GROUP BY a.City
The GROUP BY clause has an ISO-compliant syntax and a non-ISO-compliant syntax. Only one syntax style can be used in a single SELECT statement. Use the ISO compliant syntax for all new work. The non-ISO compliant syntax is provided for backward compatibility.
GROUP BY 子句具有符合 ISO 的语法和不符合 ISO 的语法。在单个 SELECT 语句中只能使用一种语法样式。对所有新作品使用符合 ISO 的语法。提供非 ISO 兼容语法是为了向后兼容。
In ISO-compliantsyntax each table or view column in any nonaggregate expression in the list must be included in the GROUP BYlist.
在符合 ISO 的语法中，任何非聚合表达式中的每个表或视图列list 必须包含在GROUP BY列表中。
select pub_id, type, avg(price), sum(total_sales)
from titles
group by pub_id, type
Refering to Organizing query results into groups: the group by clause
Sybaseor non-ISO-compliantsyntax lifts restrictions on what you can include or omit in the
selectlist of a query that includesgroup by:
The columns in the select list are not limited to the grouping columns and columns used with the vector aggregates.
The columns specified by group by are not limited to those non-aggregate columns in the select list.
Sybase或不符合 ISO 的语法取消了对您可以在
select查询列表中包含或省略的内容的限制，其中包括group by：
选择列表中的列不限于分组列和与向量聚合一起使用的列。
group by 指定的列不限于选择列表中的那些非聚合列。
Example:
例子：
select type, title_id, avg(price), avg(advance)
from titles
group by type
回答by Andreas
To use aggregate functions like sum without group by, use the over clause.
要在不使用 group by 的情况下使用 sum 等聚合函数，请使用 over 子句。
See: http://msdn.microsoft.com/en-us/library/ms189461.aspx
请参阅：http: //msdn.microsoft.com/en-us/library/ms189461.aspx
Example:
例子：
CREATE TABLE #a (ida int, name varchar(50))
CREATE TABLE #b (ida int, number int)
INSERT INTO #a VALUES(1,'one')
INSERT INTO #a VALUES(2,'two')
INSERT INTO #b VALUES(1,2)
INSERT INTO #b VALUES(1,3)
INSERT INTO #b VALUES(2,1)
SELECT DISTINCT a.ida, sum(number) OVER (PARTITION BY a.ida) FROM #a a
INNER JOIN #b b on a.ida = b.ida
