mysql - 错误代码 1111：表示无效使用组功能

Question

我尝试使用从供应商中选择的供应商.sid 离开加入目录供应商.sid=Catalog.sid where Catalog.cost>avg(Catalog.cost); 但我收到错误代码 1111：不确定我的数据库发生了什么。我对 sql 非常友好，只是在让它工作时遇到了问题。

    CREATE DATABASE CSC123Lab2;
USE CSC123Lab2;

CREATE TABLE Suppliers (
sid CHAR(20),
sname CHAR(20),
address CHAR(20),
pid CHAR(20)
);

CREATE TABLE Parts (
pid CHAR(20),
pname CHAR(20),
color CHAR(20),
sid CHAR(20)
);

CREATE TABLE Catalog (
sid CHAR(20),
pid CHAR(20),
cost REAL
);

ALTER TABLE Suppliers ADD PRIMARY KEY (sid);
ALTER TABLE Parts ADD PRIMARY KEY (pid);

INSERT INTO Suppliers (sid, sname, address) VALUES (1, "carvel", "1234 Main Street");
INSERT INTO Suppliers (sid, sname, address) VALUES (2, "coldstone", "5678 Pine Street");
INSERT INTO Suppliers (sid, sname, address) VALUES (3, "ralphs", "9876 Oak Street");
INSERT INTO Suppliers (sid, sname, address) VALUES (4, "Acme Widget", "4321 Maple Street");

INSERT INTO Parts (pid, pname, color) VALUES (1, "hammer", "red");
INSERT INTO Parts (pid, pname, color) VALUES (2, "screwdriver", "pink");
INSERT INTO Parts (pid, pname, color) VALUES (3, "nails", "green");
INSERT INTO Parts (pid, pname, color) VALUES (4, "screws", "teal");

INSERT INTO Catalog (sid, pid, cost) VALUES (1, 3, 32);
INSERT INTO Catalog (sid, pid, cost) VALUES (4, 1, 10);
INSERT INTO Catalog (sid, pid, cost) VALUES (4, 3, 48);
INSERT INTO Catalog (sid, pid, cost) VALUES (2, 4, 24);

select Suppliers.sid
from Suppliers 
left join Catalog on Suppliers.sid=Catalog.sid
where Catalog.cost>avg(Catalog.cost);


DROP TABLE Catalog;
DROP TABLE Parts;
DROP TABLE Suppliers;
DROP DATABASE CSC123Lab2;

标签： mysqlsql

Answer 1

使用 group by 并在下一级过滤

with cte  as 
(select Suppliers.sid,avg(Catalog.cost) as avg_val
from Suppliers 
left join Catalog on Suppliers.sid=Catalog.sid
group by Suppliers.sid
) select a.sid
from Catalog a join cte on a.sid=cte.sid
where a.cost>avg_val