目标：去掉同一字段下的重复数据或者仅仅留下一条
案例:
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `student` (`id`, `name`, `age`) VALUES ('1', 'cat', 22);
INSERT INTO `student` (`id`, `name`, `age`) VALUES ('2', 'dog', 33);
INSERT INTO `student` (`id`, `name`, `age`) VALUES ('3', 'fish', 44);
INSERT INTO `student` (`id`, `name`, `age`) VALUES ('4', 'cat', 55);
INSERT INTO `student` (`id`, `name`, `age`) VALUES ('5', 'dog', 11);
INSERT INTO `student` (`id`, `name`, `age`) VALUES ('6', 'cat', 45);
SELECT * FROM student;
如何编写查看有重复记录的SQL？
公式：
Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(1)>1)
案例：
SELECT * FROM student WHERE NAME IN (SELECT NAME FROM student GROUP BY NAME HAVING COUNT(1)>1)
如何编写删除重复记录的SQL?
1.重复的数据全部删除
DELETE FROM student WHERE NAME IN (
SELECT t.NAME FROM ( SELECT NAME FROM student GROUP BY NAME HAVING COUNT( 1 ) > 1 ) t
)
2.重复的数据要求删除，仅保留一条
第一步
# 删除表中删除重复数据仅保留一条
# select看看
SELECT
*
FROM
student
WHERE
id NOT IN (
SELECT
t.id
FROM
( SELECT MIN( id ) AS id FROM student GROUP BY `name` ) t
)
第二步
# delete搞定
DELETE
FROM
student
WHERE
id NOT IN ( SELECT t.id FROM ( SELECT MIN( id ) AS id FROM student GROUP BY `name` ) t )
扫一扫
900
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
