Oralce sql除了重复记录

Oralce sql去除重复记录idcity_id y_idnamicon441981391131loong9441981391131loong14上述表数据，想要根据

Oralce sql去除重复记录
id city_id y_id nam icon
441981391131loong9
441981391131loong14

上述表数据，想要根据id去除重复记录，最终结果
441981391131loong9
或者
441981391131loong14都可以。
请各位大侠帮忙。
[最优解释]


with t1 as
(
     select 44198 id,1391 city_id,131 y_id,'loong' nam,9 icon from dual
     union all
     select 44198 id,1391 city_id,131 y_id,'loong' nam,14 icon from dual
     union all
     select 44444 id,1111 city_id,222 y_id,'loong' nam,4 icon from dual
     union all
     select 44444 id,1111 city_id,222 y_id,'loong' nam,1 icon from dual
)

select id,city_id,y_id,nam,icon
from 
(
       select t1.*,row_number() over(partition by id order by rownum) rn
       from t1
)
where rn = 1


     id    city_id   y_id   nam   icon
--------------------------------------------------
1441981391131loong9
2444441111222loong4

[其他解释]
你说的通过select 查询么，暂且认为表是customer
select * from customer where id in  (select distinct(id) from customer);
[其他解释]
select distinct(id) from customer获取id为44198select * from customer where id in ('44198')结果还是两条呀。
[其他解释]
delete from table_name where (id,rowid) not in(select id,max(rowid) from table_name group by id)
[其他解释]
既然你随便去哪一个就行,那么可以这样好了
select distinct  id,city_id,y_id nam,(max)icon from 表 group by id
[其他解释]
select *
  from table_name a
where not exists (select 1
          from table_name
         where dae100 = a.dae100
          and rowid>a.rowid);
[其他解释]
select * from customer where id rowid (select max(rowid) from customer group by id );

不好意思上次的写错了，这次补上，利用物理地址。
[其他解释]
select * from customer where rowid (select max(rowid) from customer group by id );
------其他解决方案--------------------

select * from customer where rowid in (select max(rowid) from customer group by id );
[其他解释]
删除重复记录语句，重复记录只保留一条，其他的都删除，这是最高效的：

delete from table a where a.rowid!=(select max(rowid) from table  b where a.id=b.id an a.city_id=b.city_id and a.nam=b.nam);
[其他解释]
如果只需要根据id判断重不重复，只需要条件a.id=b.id,其他条件不需要。
[其他解释]
--删除重复记录
delete  from  tablename a  where  a.rowid > (select min(b.rowid)
from  tablename b  where  a.id=b.id an a.city_id=b.city_id and a.y_id=b.y_id and a.nam=b.nam);
[其他解释]

引用:

SQL code

with t1 as
(
     select 44198 id,1391 city_id,131 y_id,'loong' nam,9 icon from dual
     union all
     select 44198 id,1391 city_id,131 y_id,'loong' nam,14 icon from dual
     union……

正解
[其他解释]
楼上的不懂还误人子弟
[其他解释]
楼上的，，请不要误人子弟。。

查询不存在重复数据
select * from table1 where table1_id not in (select max (table1_id) from table2 group by table2_id having count(*)>1)
[其他解释]
参看http://topic.csdn.net/u/20071224/10/b20e2cf3-85e1-4f2e-b368-afbc28e15cc2.html
[其他解释]
假设你的表是emp表，依据部门号去重，可以这样做，依据rowid
SELECT *
  FROM EMP T
WHERE (T.EMPNO, ROWID) IN
       (SELECT T.EMPNO, MAX(ROWID) FROM EMP T GROUP BY T.EMPNO)
[其他解释]
delete from T a where a.rowid > (select min(b.rowid)
from T b where a.id=b.id and a.city_id=b.city_id and a.y_id=b.y_id and a.nam=b.nam);
[其他解释]
delete from cs_ls
where rowid not in
       (select max(rowid) from cs_ls a group by a.id);
/*按id排序，取rowid最大值，将not in 最大值的记录删除*/

rowid是一个伪列，是用来确保表中行的唯一性.

[其他解释]
可以分三步：
1.首选创建一个临时表
create table temp as select * from user group by id ;
2.删除原表user
drop table user;
3.在将临时表重命名
alter table temp rename user;

个人觉得针对表中数据少时。
[其他解释]
删除重复数据的一个小问题,惹得大家这么高兴....

上面提供了好几种方法,有炫耀技术剑走偏锋的,有中规中矩的,也有大智若愚的