SQL 列的原因在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13999817/
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
Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
提问by david blaine
I got an error -
我有一个错误 -
Column 'Employee.EmpID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
列“Employee.EmpID”在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中。
select loc.LocationID, emp.EmpID
from Employee as emp full join Location as loc
on emp.LocationID = loc.LocationID
group by loc.LocationID
This situation fits into the answer given by Bill Karwin.
这种情况符合 Bill Karwin 给出的答案。
correction for above, fits into answer by ExactaBox -
以上更正，符合 ExactaBox 的答案 -
select loc.LocationID, count(emp.EmpID) -- not count(*), don't want to count nulls
from Employee as emp full join Location as loc
on emp.LocationID = loc.LocationID
group by loc.LocationID
ORIGINAL QUESTION -
原始问题 -
For the SQL query -
对于 SQL 查询 -
select *
from Employee as emp full join Location as loc
on emp.LocationID = loc.LocationID
group by (loc.LocationID)
I don't understand why I get this error. All I want to do is join the tables and then group all the employees in a particular location together.
我不明白为什么我会收到这个错误。我想要做的就是加入表格，然后将特定位置的所有员工分组在一起。
I think I have a partial explanation for my own question. Tell me if its ok -
我想我对我自己的问题有部分解释。告诉我是否可以 -
To group all employees that work in the same location we have to first mention the LocationID.
要将在同一地点工作的所有员工分组，我们必须首先提及 LocationID。
Then, we cannot/do not mention each employee ID next to it. Rather, we mention the total number of employees in that location, ie we should SUM() the employees working in that location. Why do we do it the latter way, i am not sure. So, this explains the "it is not contained in either an aggregate function" part of the error.
然后，我们不能/不要在它旁边提到每个员工 ID。相反，我们提到了该位置的员工总数，即我们应该 SUM() 在该位置工作的员工。为什么我们采用后一种方式，我不确定。因此，这解释了错误的“它不包含在聚合函数中”部分。
What is the explanation for the GROUP BYclause part of the error ?
GROUP BY错误的子句部分的解释是什么？
回答by Bill Karwin
Suppose I have the following table T:
假设我有下表T：
a b
--------
1 abc
1 def
1 ghi
2 jkl
2 mno
2 pqr
And I do the following query:
我执行以下查询：
SELECT a, b
FROM T
GROUP BY a
The output should have two rows, one row where a=1and a second row where a=2.
输出应该有两行，一行 wherea=1和第二行 where a=2。
But what should the value of bshow on each of these two rows? There are three possibilities in each case, and nothing in the query makes it clear which value to choose for b in each group. It's ambiguous.
但是b的值应该在这两行的每一行上显示什么？每种情况都有三种可能性，查询中没有任何内容明确为每个组中的 b 选择哪个值。这是模棱两可的。
This demonstrates the single-value rule, which prohibits the undefined results you get when you run a GROUP BY query, and you include any columns in the select-list that are neither part of the grouping criteria, nor appear in aggregate functions (SUM, MIN, MAX, etc.).
这演示了单值规则，它禁止您在运行 GROUP BY 查询时获得未定义的结果，并且您在选择列表中包含既不属于分组条件的一部分也不出现在聚合函数中的任何列（SUM、 MIN、MAX 等）。
Fixing it might look like this:
修复它可能看起来像这样：
SELECT a, MAX(b) AS x
FROM T
GROUP BY a
Now it's clear that you want the following result:
现在很明显您想要以下结果：
a x
--------
1 ghi
2 pqr
回答by John Woo
Your query will work in MYSQLif you set to disable ONLY_FULL_GROUP_BYserver mode (and by default It is). But in this case, you are using different RDBMS. So to make your query work, add all non-aggregated columnsto your GROUP BYclause, eg
MYSQL如果您设置为禁用ONLY_FULL_GROUP_BY服务器模式（默认情况下为），您的查询将起作用。但在本例中，您使用的是不同的 RDBMS。因此，要使您的查询工作，请将所有非聚合列添加到您的GROUP BY子句中，例如
SELECT col1, col2, SUM(col3) totalSUM
FROM tableName
GROUP BY col1, col2
Non-Aggregated columns means the column is not pass into aggregated functions like SUM, MAX, COUNT, etc..
非聚集列装置中的列不传递到像聚合函数SUM，MAX，COUNT等。
回答by ExactaBox
"All I want to do is join the tables and then group all the employees in a particular location together."
“我想要做的就是加入表格，然后将特定位置的所有员工组合在一起。”
It sounds like what you want is for the output of the SQL statement to list every employee in the company, but first all the people in the Anaheim office, then the people in the Buffalo office, then the people in the Cleveland office (A, B, C, get it, obviously I don't know what locations you have).
听起来您想要的是 SQL 语句的输出列出公司中的每个员工，但首先是阿纳海姆办公室的所有人员，然后是布法罗办公室的人员，然后是克利夫兰办公室的人员（A， B，C，明白了，显然我不知道你有什么位置）。
In that case, lose the GROUP BY statement. All you need is ORDER BY loc.LocationID
在这种情况下，丢失 GROUP BY 语句。所有你需要的是 ORDER BY loc.LocationID
回答by Alex W
Basically, what this error is saying is that if you are going to use the GROUP BYclause, then your result is going to be a relation/table with a row for each group, so in your SELECTstatement you can only "select" the column that you are grouping by and use aggregate functions on that column because the other columns will not appear in the resulting table.
基本上，这个错误的意思是，如果您要使用该GROUP BY子句，那么您的结果将是一个关系/表，每个组都有一行，因此在您的SELECT语句中，您只能“选择”您要使用的列正在按该列分组并使用聚合函数，因为其他列不会出现在结果表中。
