mysql删除重复数据

目标：删除数据表中bookname和authorID同时重复的多余数据，只保留其中的任意一条数据
给出一个数据库例子

思路：
删除之前，先select查看需要删除的数据是否为目标数据

1、首先找到表中重复的目标数据

利用group by方法同时对两列进行分组查找
having后面使用count()对指定列中数据出现次数进行比较

SELECT *
FROM book
GROUP BY bookname,authorID 
HAVING COUNT(*)>1；

显示结果并不符合预期，查找原因：
可能由于显示问题该结果整合了数据，只显示一条

用concat()函数检查一下：

SELECT bookname,authorID,GROUP_CONCAT(bookID)
FROM book
GROUP BY bookname,authorID 
HAVING COUNT(*)>1；

得出：

所以结果为显示问题而非方法错误

2、如果按照1选出的数据删除则所有重复数据都删除而不会保留唯一一条数据

由于取出的数据是多条的，但是id是不重复的
可以利用min()或者max()方法仅取出一个id，并按照id保留数据

SELECT MIN(bookID)
	FROM book
	GROUP BY bookname,authorID
	HAVING COUNT(*)>1；

可以看到取出了一个id

3、将1，2中的数据组合成需要删除的数据

需要删除的数据是在1中且不在2中的数据
使用in()和not in()方法进行筛选

SELECT *FROM book
WHERE (bookname,authorID) IN (
	SELECT bookname,authorID
	FROM book
	GROUP BY bookname,authorID
	HAVING COUNT(*)>1)
	AND bookID NOT IN(
	SELECT MIN(bookID)
	FROM book
	GROUP BY bookname,authorID
	HAVING COUNT(*)>1)

得出结果，符合需要删除的要求

4、删除符合要求的结果数据

DELETE FROM book
WHERE (bookname,authorID) IN (
	SELECT bookname,authorID
	FROM book
	GROUP BY bookname,authorID
	HAVING COUNT(*)>1)
	AND bookID NOT IN(
	SELECT MIN(bookID)
	FROM book
	GROUP BY bookname,authorID
	HAVING COUNT(*)>1)

报错

查询：delete from book WHERE (bookname,authorID) IN ( SELECT bookname,authorID FROM book GROUP BY bookname,authorID HAVING COUNT(*)>1)...错误代码： 1093
You can't specify target table 'book' for update in FROM clause

查找原因：
错误代码为1093，查找后得知mysql对子查询的支持是比较薄弱的

解决办法：
将查到的数据取别名，多加一个嵌套

取别名

SELECT bookID FROM (
SELECT *FROM book
WHERE (bookname,authorID) IN (
	SELECT bookname,authorID
	FROM book
	GROUP BY bookname,authorID
	HAVING COUNT(*)>1)
	AND bookID NOT IN(
	SELECT MIN(bookID)
	FROM book
	GROUP BY bookname,authorID
	HAVING COUNT(*)>1)
	)  AS t

嵌套完成删除操作：

DELETE FROM book WHERE bookID IN (
SELECT bookID FROM (
SELECT *FROM book
WHERE (bookname,authorID) IN (
	SELECT bookname,authorID
	FROM book
	GROUP BY bookname,authorID
	HAVING COUNT(*)>1)
	AND bookID NOT IN(
	SELECT MIN(bookID)
	FROM book
	GROUP BY bookname,authorID
	HAVING COUNT(*)>1)
	)  AS t)