表Dept;
id name
001 sed
002 df
003 fdfd
003 dge
001 lo
004 uy
(1)如果id要建立索引,请写出sql语句查询出数据表所有有重复字段的列id?
(2)请用一个sql或者一组sql语句实现对重复数据的删除,比如(003 fdfd)保留而(003 dge)被删除?
id name
001 sed
002 df
003 fdfd
003 dge
001 lo
004 uy
(1)如果id要建立索引,请写出sql语句查询出数据表所有有重复字段的列id?
(2)请用一个sql或者一组sql语句实现对重复数据的删除,比如(003 fdfd)保留而(003 dge)被删除?
select id
from tb
group by id
having count(*) >12.
delete tb
where id in(
select id
from (select id,row_number() over(partition by id) rn from tb)
where rn>1);
SQL> select id, row_number() over(partition by id) rn
2 from (select 1 id, 'sed' name
3 from dual
4 union
5 select 1 id, 'xed' name
6 from dual
7 union
8 select 2 id, 'eed' name
9 from dual
10 union
11 select 3 id, 'abc' name
12 from dual
13 union
14 select 4 id, 'def' name
15 from dual
16 union
17 select 3 id, 'qqq' name from dual);
select id, row_number() over(partition by id) rn
*
ERROR 位于第 1 行:
ORA-30485: 在窗口说明中丢失 ORDER BY 表达式
怎么不能执行呢?