MySQL：组函数的使用无效

Question

我正在使用MySQL。这是我的架构：

供应商（sid：整数，sname：字符串，地址字符串）

零件（pid：整数，pname：字符串，颜色：字符串）

目录（ >sid：整数，pid：整数，成本：实数）

（主键加粗）

我正在尝试编写一个查询来选择至少由两个供应商制造的所有零件：

-- 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
);

首先，我什至要去吗关于这个正确的方法是什么？

其次，我得到这个错误：

1111 - 群组功能使用无效

我做错了什么？

Answer 1

您需要使用HAVING，而不是WHERE。

不同之处在于：WHERE子句过滤MySQL选择的行。 然后 MySQL 将行分组在一起并聚合 COUNT 函数的数字。

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)

Answer 2

首先，您收到的错误是由于您使用 COUNT 函数的位置造成的 - 您不能在 WHERE 中使用聚合（或组）函数条款。

其次，不要使用子查询，只需将表连接到自身：

SELECT a.pid 
FROM Catalog as a LEFT JOIN Catalog as b USING( pid )
WHERE a.sid != b.sid
GROUP BY a.pid

我认为应该只返回至少有两行具有相同 pid 的行，但至少有 2 个 sid 。为了确保每个 pid 仅返回一行，我应用了分组子句。

Answer 3

如果您的 where 子句中没有聚合函数，1111 - Invalid use of group function 错误的另一个可能来源是您有嵌套聚合函数：

select sum(avg(close)) from prices;
(1111, 'Invalid use of group function')

您可以通过分解来解决此问题将问题分为两个步骤：

1. 将内部聚合保存到变量中

select @avg:=avg(close) from prices;

2. 对变量运行外部聚合

select sum(@avg) from prices;