id city_id y_id nam icon
44198 1391 131 loong 9
44198 1391 131 loong 14
上述表数据，想要根据id去除重复记录，最终结果
44198 1391 131 loong 9
或者
44198 1391 131 loong 14都可以。
请各位大侠帮忙。
------最佳解决方案--------------------
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
--------------------------------------------------
1 44198 1391 131 loong 9
2 44444 1111 222 loong 4
------其他解决方案--------------------
你说的通过select 查询么，暂且认为表是customer
select * from customer where id in (select distinct(id) from customer);
------其他解决方案--------------------
select distinct(id) from customer获取id为44198 select * 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 );
------其他解决方案--------------------