场景如下:
t_user
id email status
1 aa@gmail.com 1
2 aa@gmail.com 1
3 aa@gmail.com 2
4 yy@gmail.com 1
5 yy@gmail.com 2
6 zz@gmail.com 2
7 zz@gmail.com 3
8 zz@gmail.com 4
如何写一个sql语句将t_user变成如下
2 aa@gmail.com 1
4 yy@gmail.com 1
6 zz@gmail.com 2
------解决方案--------------------
DELETE FROM t_user E WHERE E.status > (SELECT MIN(X.status)
FROM t_user X HERE X.email = E.email );
------解决方案--------------------
- SQL code
--测试表创建create table t_user( id int, email varchar2(20), status int)--测试数据插入insert into t_user(select 1,'aa@gmail.com',1 from dual union allselect 2,'aa@gmail.com',1 from dual union allselect 3,'aa@gmail.com',2 from dual union allselect 4,'yy@gmail.com',1 from dual union allselect 5,'yy@gmail.com',2 from dual union allselect 6,'zz@gmail.com',2 from dual union allselect 7,'zz@gmail.com',3 from dual union allselect 8,'zz@gmail.com',4 from dual union allselect 2,'aa@gmail.com',1 from dual union allselect 4,'yy@gmail.com',1 from dual union allselect 6,'zz@gmail.com',2 from dual)--下面是我的处理思路:--1.首先创建一个和原表结构一模一样的表create table t_user_bak as select *from t_user--2.重复数据的查询,并且复制给复制表(t_user_bak),此时)t_user_bak里面的数据就是t_user去掉重复的数据insert into t_user_bak select min(id) as id,email,min(status) as status from t_user group by email--子句(select min(id) as id,email,min(status) as status from t_user group by email)查询结果4 yy@gmail.com 16 zz@gmail.com 2--3.删除t_user所有的数据--4.把t_user_bak的数据又复制回t_user表--以上步骤能完成去除表数据重复功能!1 aa@gmail.com 1