SQL查重去重

这可以说算是比较经典的面试题了
往往会先问一个简单的问题 然后在一层层延伸…

1. SQL查重

## **1.1 重复的依据：login_name 相同**

SELECT login_name, count(login_name) FROM sys_user_bak GROUP BY login_name HAVING count(login_name) > 1;

2. SQL去重

2.1 删除重复的

DELETE FROM sys_user_bak GROUP BY login_name  HAVING count(login_name) > 1;  -- 错误sql

错误截图

– 正确删除sql

DELETE FROM sys_user_bak
WHERE login_name IN (
	SELECT t.login_name FROM (SELECT login_name FROM sys_user_bak GROUP BY login_name  HAVING count(login_name) > 1 ) t
)

2.2 删除重复的，并保留其中一条数据

DELETE 
FROM sys_user_bak
WHERE login_name IN (
	SELECT t.login_name FROM (SELECT login_name FROM sys_user_bak GROUP BY login_name  HAVING count(login_name) > 1 ) t
)
AND id NOT IN (
  SELECT dt.id_max FROM (SELECT max(id) AS id_max FROM sys_user_bak GROUP BY login_name HAVING count(login_name) > 1 ) dt 
);