mysql删除重复数据，并保留一条

表结构如下：删除相同type的数据。

CREATE TABLE `work_sys_menu` (
  `menu_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `type` int(11) DEFAULT NULL COMMENT '类型',
  PRIMARY KEY (`menu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8 COMMENT='菜单信息';

 

删除SQL:

DELETE FROM work_sys_menu WHERE type IN 
    ( SELECT a.type FROM (SELECT type FROM work_sys_menu GROUP BY type HAVING COUNT(type) > 1) a)
    AND menu_id NOT IN (SELECT b.menu_id FROM(SELECT menu_id FROM work_sys_menu GROUP BY type HAVING COUNT(*) > 1) b);