创建表

CREATE TABLE `products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `price` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入数据，其中“橘子”和“苹果”各有一个重复行

INSERT INTO `products` VALUES (5, ‘橘子‘, 100.00);INSERT INTO `products` VALUES (6, ‘橘子‘, 100.00);INSERT INTO `products` VALUES (7, ‘苹果‘, 50.00);INSERT INTO `products` VALUES (8, ‘苹果‘, 50.00);INSERT INTO `products` VALUES (9, ‘香蕉‘, 80.00);

删除重复行

DELETE FROM products p1 WHERE p1.id < ( SELECT max_id FROM ( SELECT max( p2.id ) max_id FROM products p2 WHERE p1.NAME = p2.NAME AND p1.price = p2.price ) AS a  )

注意红色高亮部分写法，由于mysql不支持在自查询中执行update操作，需select 的结果再通过一个中间表select一次。否则会出现如下错误

You can‘t specify target table ‘p1‘ for update in FROM clause