这个重复如何取去除，不是所有字段重复的哦

这个重复怎么取去除，不是所有字段重复的哦
这个重复怎么取去除只留一条呢，不是所有字段重复的哦

SQL code

create table (aa varchar2(20)  primary key,bb varchar2(20),cc varchar2(20),);insert into tableselect '001','小明','xiaoming@d.com' from dualunion allselect '010','小明','xiaoming@d.com' from dualunion allselect '002','小红','xiaohong@d.com' from dualunion allselect '022','小红','xiaohong@d.com' from dualunion allselect '301','小明','xiaoming@d.com' from dual

我要的结果
小明 xiaoming@d.com
小红 xiaohong@d.com

怎么删除其余多出的数据呢？

[解决办法]
或者你试一下这个：

delete from table_name t1 where t1.rowid != 
(
select max(t2.rowid) from table_name t2
where t1.bb = t2.bb and t1.cc = t2.cc
) ;

commit;

[解决办法]
把要留下的id查找出来如：select min(aa) from table group by bb,cc
，然后再删除
[解决办法]

SQL code

select distinct bb,cc from t;
[解决办法]
SQL code
WITH t AS(select '001' a,'小明' b,'xiaoming@d.com' c from dualunion allselect '010','小明','xiaoming@d.com' from dualunion allselect '002','小红','xiaohong@d.com' from dualunion allselect '022','小红','xiaohong@d.com' from dualunion allselect '301','小明','xiaoming@d.com' from dual)1.SELECT distinct b,c from t;2.SELECT MIN(b),MIN(c) FROM t GROUP BY  b,c;3.SELECT b,cFROM       (SELECT b,c,row_number() over(PARTITION BY b,c ORDER BY b,c)rn FROM t) WHERE rn=1--你自己挑吧!
[解决办法]
SQL code
select *  from tab1 t where (t.bb, t.cc) in       (select bb, cc from tab1 group by bb, cc having count(*) > 1)   and rowid  in (select min(rowid)                       from tab1                      group by bb,cc                     having count(*) > 1);
[解决办法]
实测成功：
SQL code
create table T19(    aa varchar2(20)  primary key,    bb varchar2(20),    cc varchar2(20));insert into TABLESELECT MAX(NAME), MAX(Email) FROM (select '001' ID,  '小明' NAME,'xiaoming@d.com' Email from dualunion allselect '010' ID,'小明' NAME,'xiaoming@d.com' Email from dualunion allselect '002' ID,'小红' NAME,'xiaohong@d.com' Email from dualunion allselect '022' ID,'小红' NAME,'xiaohong@d.com' Email from dualunion allselect '301' ID,'小明' NAME,'xiaoming@d.com' Email from dual)GROUP BY NAME, Email; 

 

查看更多 下一篇