MySQL:无效使用组功能

2021-11-20 00:00:00 sql mysql mysql-error-1111

我正在使用 MySQL.这是我的架构:

I am using MySQL. Here is my schema:

Suppliers(sid: integer, sname: string, address string)

Suppliers(sid: integer, sname: string, address string)

Parts(pid: integer, pname: string, color: string)

Parts(pid: integer, pname: string, color: string)

目录(sid:整数，pid:整数，成本:实数)

(主键加粗)

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

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?

其次，我收到此错误:

1111 - 组函数的使用无效

1111 - Invalid use of group function

我做错了什么?

推荐答案

你需要使用HAVING，而不是WHERE.

区别在于:WHERE 子句过滤 MySQL 选择的行.然后 MySQL 将行组合在一起并聚合 COUNT 函数的数字.

The difference is: the WHERE clause filters which rows MySQL selects. Then MySQL groups the rows together and aggregates the numbers for your COUNT function.

HAVING 就像 WHERE，只是它发生在之后COUNT 值被计算出来，所以它'会像你期望的那样工作.将您的子查询重写为:

HAVING is like WHERE, only it happens after the COUNT value has been computed, so it'll work as you expect. Rewrite your subquery as:

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

相关文章