用SQL实现excel的一些数据分析功能

注：代码在MySQL数据库中执行

1 重复数据处理

查找重复记录

SELECT *FROM user
Where (nick_name,password)in
(
SELECT nick_name,password
FROM user
group by nick_name,password
having count(nick_name)>1
);

查找去重记录 查找id最大的记录

SELECT *FROM user
WHERE id in
(SELECT max(id)FROM user
group by nick_name,password
having count(nick_name)>1
);

删除重复记录 只保留id值最小的记录

DELETE c1
FROM customer c1,customer c2
WHERE c1.cust_email=c2.cust_email
AND c1.id>c2.id;
DELETE FROM user Where (nick_name,password)in
(SELECT nick_name,password FROM
(SELECT nick_name,password FROM user