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都可以。
请各位大侠帮忙。
44198 1391 131 loong 9
44198 1391 131 loong 14上述表数据,想要根据id去除重复记录,最终结果
44198 1391 131 loong 9
或者
44198 1391 131 loong 14都可以。
请各位大侠帮忙。
select * from customer where id in (select distinct(id) from customer);
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 distinct id,city_id,y_id nam,(max)icon from 表 group by id
from table_name a
where not exists (select 1
from table_name
where dae100 = a.dae100
and rowid>a.rowid);
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);
select * from table1 where table1_id not in (select max (table1_id) from table2 group by table2_id having count(*)>1)
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);
SELECT *
FROM EMP T
WHERE (T.EMPNO, ROWID) IN
(SELECT T.EMPNO, MAX(ROWID) FROM EMP T GROUP BY T.EMPNO)
1.首选创建一个临时表
create table temp as select * from user group by id ;
2.删除原表user
drop table user;
3.在将临时表重命名
alter table temp rename user;个人觉得针对表中数据少时。
where rowid not in
(select max(rowid) from cs_ls a group by a.id);
/*按id排序,取rowid最大值,将not in 最大值的记录删除*/rowid是一个伪列,是用来确保表中行的唯一性.