有个设备表ds_device,有两个字段:
CREATE TABLE `ds_device` (
`id` bigint(20) NOT NULL,
`device_code` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
KEY `device_code` (`device_code`)
)
device_code有重复的,我想要把这个表里面所有device_code重复的删除掉,保留一条,sql如下:
DELETE FROM ds_device WHERE device_code IN (SELECT device_code FROM (SELECT device_code, COUNT(device_code) AS COUNT FROM ds_device
GROUP BY device_code HAVING COUNT> 1) AS tab)