数据表id name number001 aa 001
002 ss 001
003 gg 002
004 kk 001求SQL一个
将number相同的数据保留一条,剩下的取出来。
上面的数据 结果为
002 ss 001
004 kk 001
或者
001 aa 001
002 ss 001就是原库保留一条,剩下的取出来
002 ss 001
003 gg 002
004 kk 001求SQL一个
将number相同的数据保留一条,剩下的取出来。
上面的数据 结果为
002 ss 001
004 kk 001
或者
001 aa 001
002 ss 001就是原库保留一条,剩下的取出来
select min(id),min(name),number
from tablename
group by number
from testtable
where id not in (select max(id) from testtable t group by t.number)
from testtable
where id not in (select max(id) from testtable t group by t.number)
需要把not去掉
select number,min(id),min(name)
from tb
group by number
from tablename
group by number;
我要的刚是原表和你这个SQL取出的数据的差集
from (select id ,name, number,
row_number() over(partition by number order by dbms_random.value) rn
from tb)
where rn>1
union select '002', 'ss', '001' from dual
union select '003', 'gg', '002' from dual
union select '004', 'kk', '001' from dual)
select a.*
from t a
where a.id not in (select max(b.id) as id from t b group by b.num);
union select '002', 'ss', '001' from dual
union select '003', 'gg', '002' from dual
union select '004', 'kk', '001' from dual)
select *
from (select t.*,
row_number() over(partition by num order by id) rn
from t)
where rn <> 1