场景如下:
t_user
id email status
1 [email protected] 1
2 [email protected] 1
3 [email protected] 2
4 [email protected] 1
5 [email protected] 2
6 [email protected] 2
7 [email protected] 3
8 [email protected] 4
如何写一个sql语句将t_user变成如下
2 [email protected] 1
4 [email protected] 1
6 [email protected] 2
t_user
id email status
1 [email protected] 1
2 [email protected] 1
3 [email protected] 2
4 [email protected] 1
5 [email protected] 2
6 [email protected] 2
7 [email protected] 3
8 [email protected] 4
如何写一个sql语句将t_user变成如下
2 [email protected] 1
4 [email protected] 1
6 [email protected] 2
FROM t_user X HERE X.email = E.email );
delete from t_user
where email in (select email from people group by email having count(email) > 1)
and rowid not in (select min(rowid) from t_user group by email having count(email)>1)
( id number,
email varchar2(20),
status varchar2(1)
)insert into t_user(id,email,status) values (1,'[email protected]','1');
insert into t_user(id,email,status) values (2,'[email protected]','1');
insert into t_user(id,email,status) values (3,'[email protected]','2');
insert into t_user(id,email,status) values (4,'[email protected]','1');
insert into t_user(id,email,status) values (5,'[email protected]','2');
insert into t_user(id,email,status) values (6,'[email protected]','2');
insert into t_user(id,email,status) values (7,'[email protected]','3');
insert into t_user(id,email,status) values (8,'[email protected]','4');
commit;select * from t_user where id in (2,4,6);
1 [email protected] 1
这条记录每删掉呀。
--测试表创建
create table t_user
(
id int,
email varchar2(20),
status int
)
--测试数据插入
insert into t_user(
select 1,'[email protected]',1 from dual union all
select 2,'[email protected]',1 from dual union all
select 3,'[email protected]',2 from dual union all
select 4,'[email protected]',1 from dual union all
select 5,'[email protected]',2 from dual union all
select 6,'[email protected]',2 from dual union all
select 7,'[email protected]',3 from dual union all
select 8,'[email protected]',4 from dual union all
select 2,'[email protected]',1 from dual union all
select 4,'[email protected]',1 from dual union all
select 6,'[email protected]',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 [email protected] 1
6 [email protected] 2
--3.删除t_user所有的数据
--4.把t_user_bak的数据又复制回t_user表
--以上步骤能完成去除表数据重复功能!
1 [email protected] 1
--ps1:上面编辑太快 格式有些扭曲,三行查询结果没到一块,这里重新布局
4 [email protected] 1
6 [email protected] 2
1 [email protected] 1
--ps2:以上过程全部测试通过!
--这里再追问一下:
--楼主你提供的数据里面status字段保留的是重复的一类email里面最小的
--但是你的id是根据什么规律来保留的不清楚,保留最小的为偶数的id?还是任意保留一个id?
FROM t_user X wHERE X.email = E.email )) or (e.id >( select min(k.id) from t_user k where E.email =k.email));
from table1 b where a.name1=b.name1 and a.name2=b.name2......)