Question

oracle数据库删除重复行记录并保留一行的SQL语句怎么写?

Answer 1

delete from tab where ROWID IN (
SELECT ROWID FROM (SELECT RANK()OVER(PARTITION BY 重复字段 ORDER BY ROWID) t
FROM tab WHERE 重复字段 IN (select name1 from tab group by 重复字段 HAVING COUNT(重复字段)>1)) WHERE t>1);

追问

查询数据库表中第15行的记录SQL语句怎么写？

追答

delete from tab where rowid=(
select ii from (select ROWNUM nn,ROWID ii from tab WHERE ROWNUM<=15) WHERE nn=15);

追问

我只要查出第15行的记录，不要删除

追答

select * from (select rownum nn,tab.* from tab WHERE rownum<=15) WHERE nn=15);

Answer 2

delete tab a where rowid<>(select max(rowid) from tab b where a.c1=a.c1 and a.c2=b.c2 and .....)