with t as
(select 1 id, 1 value
from dual
union all
select 1 id, 2 value
from dual
union all
select 2 id, 1 value
from dual
union all
select 2 id, 3 value
from dual
union all
select 2 id, 2 value from dual)
select t1.id, t1.value
from (select t.*,
row_number() over(partition by id order by value desc) rn
from t) t1
where t1.rn = 1;
(select 1 id, 1 value
from dual
union all
select 1 id, 2 value
from dual
union all
select 2 id, 1 value
from dual
union all
select 2 id, 3 value
from dual
union all
select 2 id, 2 value from dual)
select t1.id, t1.value
from (select t.*,
row_number() over(partition by id order by value desc) rn
from t) t1
where t1.rn = 1;
FROM 表名
GROUP BY id;
这个很经典
delete from 表名
where ID in (select ID from 表名 group by ID having count(ID) > 1)
and value not in (select min(value) from 表名 group by ID having count(ID)>1)