我所做的是创建一个名为Rent2的临时表,以存放我的客户注册的所有重复模块,然后在删除临时表之前执行delete内部联接语句。
但在完成这项工作后,总会有一个剩余的副本。我在这里做错了什么?
CREATE TABLE IF NOT EXISTS `tblRent2` (
`IdRent` INT(11) NOT NULL)
ENGINE = InnoDB;
INSERT INTO tblRent2 (SELECT IdRent FROM tblRent WHERE IdModule = 1 GROUP BY IdClient HAVING COUNT(IdModule) > 1);
INSERT INTO tblRent2 (SELECT IdRent FROM tblRent WHERE IdModule = 2 GROUP BY IdClient HAVING COUNT(IdModule) > 1);
INSERT INTO tblRent2 (SELECT IdRent FROM tblRent WHERE IdModule = 3 GROUP BY IdClient HAVING COUNT(IdModule) > 1);
INSERT INTO tblRent2 (SELECT IdRent FROM tblRent WHERE IdModule = 4 GROUP BY IdClient HAVING COUNT(IdModule) > 1);
INSERT INTO tblRent2 (SELECT IdRent FROM tblRent WHERE IdModule = 5 GROUP BY IdClient HAVING COUNT(IdModule) > 1);
INSERT INTO tblRent2 (SELECT IdRent FROM tblRent WHERE IdModule = 6 GROUP BY IdClient HAVING COUNT(IdModule) > 1);
INSERT INTO tblRent2 (SELECT IdRent FROM tblRent WHERE IdModule = 999 GROUP BY IdClient HAVING COUNT(IdModule) > 1);
DELETE r.* FROM tblRent r INNER JOIN tblRent2 r2 ON
r.idRent = r2.idRent;
SELECT * FROM tblRent2;
DROP TABLE tblRent2;
原始表格如下所示:
IdRent | IDClient | IdModule
1 | 30 | 999
2 | 30 | 999
3 | 31 | 1
4 | 31 | 1
这些都是主键。
更好的问题是为什么要使用临时表?
只需加入表格本身:
DELETE r2
FROM tblRent r
JOIN tblRent r2
ON r.IDClient = r2.IDClient
AND r.IdModule = r2.IdModule
AND r.idRent < r2.idRent
这也将清理三个副本,总是留下具有最低idRent
的行。
一个关键点(请原谅双关语)是额外的连接条件r.idRent < r2.idRent
,它防止行连接到它们自己(通过具有不相等的id),并选择最后添加的行进行删除(通过强制一个id小于另一个id)。使用r.idRent != r2.idRent
会导致两个重复项都被删除。
我认为您的删除查询有缺陷。Delete不需要在Delete关键字后面加星号。您可以使用此查询。
DELETE FROM tblRent WHERE idRent in (SELECT IdRent FROM tblRent2);