gpt4 book ai didi

mysql - Delete Using Multiple Tables 并在子查询中重复表

转载 作者:行者123 更新时间:2023-11-29 02:20:27 24 4
gpt4 key购买 nike

我想解雇销售额较低的部门的所有员工

架构和Sql fiddle

CREATE TABLE Employee
(`ID` int, `name` varchar(6), `deptID` int);

INSERT INTO Employee
(`ID`, `name`, `deptID`)
VALUES
(1, 'Jhon', NULL), (2, 'Luis', 1),
(3, 'Angela', 1), (4, 'Peter', NULL),
(5, 'Sonia', 4), (6, 'Oliver', 4);

CREATE TABLE Sales
(`ID` int, `Sales` int);

INSERT INTO Sales
(`ID`, `Sales`)
VALUES
(1, 100), (2, 300),
(3, 500), (4, 600),
(5, 250), (6, 150);

我可以做这样的事情

DELETE E 
FROM Employee E
INNER JOIN Sales S
ON E.`ID` = S.`ID`
WHERE `SALES` = 600;

我想要什么

DELETE E1 
FROM Employee E1
WHERE `deptID` IN (
SELECT `deptID`
FROM Employee E
Inner JOIN Sales S
ON E.`ID` = S.`ID`
GROUP BY `deptID`
HAVING SUM(`Sales`) <= 400
);

但是我不能像 manual 中描述的那样在内部 SELECT 中使用 Employee

Subqueries
Currently, you cannot delete from a table and select from the same table in a subquery.

那么正确的语法或解决方法是什么?

最佳答案

使用JOIN

DELETE e1
FROM Employee AS e1
JOIN (SELECT deptID
FROM Employee AS e
JOIN Sales AS s ON e.ID = s.ID
GROUP BY deptID
HAVING SUM(Sales) <= 400) AS d
ON e1.deptID = d.deptID

关于mysql - Delete Using Multiple Tables 并在子查询中重复表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/32770114/

24 4 0