MySQL：无效使用组功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2330840/
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
MySQL: Invalid use of group function
提问by Nick Heiner
I am using MySQL. Here is my schema:
我正在使用 MySQL。这是我的架构：
Suppliers(sid: integer, sname: string, address string)
供应商( sid: integer, sname: string, address string)
Parts(pid: integer, pname: string, color: string)
零件（pid：整数，pname：字符串，颜色：字符串）
Catalog(sid: integer, pid: integer, cost: real)
目录（sid：整数，pid：整数，成本：实数）
(primary keys are bolded)
（主键加粗）
I am trying to write a query to select all parts that are made by at least two suppliers:
我正在尝试编写一个查询来选择至少由两个供应商制造的所有零件：
-- Find the pids of parts supplied by at least two different suppliers.
SELECT c1.pid -- select the pid
FROM Catalog AS c1 -- from the Catalog table
WHERE c1.pid IN ( -- where that pid is in the set:
SELECT c2.pid -- of pids
FROM Catalog AS c2 -- from catalog
WHERE c2.pid = c1.pid AND COUNT(c2.sid) >= 2 -- where there are at least two corresponding sids
);
First off, am I even going about this the right way?
首先，我是否以正确的方式解决这个问题？
Secondly, I get this error:
其次，我收到此错误：
1111 - Invalid use of group function
1111 - 组功能的使用无效
What am I doing wrong?
我究竟做错了什么？
回答by rjh
You need to use HAVING, not WHERE.
您需要使用HAVING，而不是WHERE。
The difference is: the WHEREclause filters which rows MySQL selects. ThenMySQL groups the rows together and aggregates the numbers for your COUNTfunction.
区别在于：WHERE子句过滤 MySQL 选择的行。然后MySQL 将行分组在一起并聚合COUNT函数的数字。
HAVINGis like WHERE, only it happens afterthe COUNTvalue has been computed, so it'll work as you expect. Rewrite your subquery as:
HAVING就像WHERE，只有在计算完值后才会发生COUNT，因此它会按您的预期工作。将您的子查询重写为：
( -- where that pid is in the set:
SELECT c2.pid -- of pids
FROM Catalog AS c2 -- from catalog
WHERE c2.pid = c1.pid
HAVING COUNT(c2.sid) >= 2)
回答by Mark Elliot
First, the error you're getting is due to where you're using the COUNTfunction -- you can't use an aggregate (or group) function in the WHEREclause.
首先，您得到的错误是由于您在何处使用该COUNT函数——您不能在WHERE子句中使用聚合（或组）函数。
Second, instead of using a subquery, simply join the table to itself:
其次，不要使用子查询，只需将表连接到自身：
SELECT a.pid
FROM Catalog as a LEFT JOIN Catalog as b USING( pid )
WHERE a.sid != b.sid
GROUP BY a.pid
Which I believe should return only rows where at least two rows exist with the same pidbut there is are at least 2 sids. To make sure you get back only one row per pidI've applied a grouping clause.
我认为应该只返回至少存在两行相同pid但至少有 2 行的行sid。为了确保每次只返回一行，pid我应用了分组子句。
