我正在尝试删除重复的行；没有办法；我一直收到错误(Oracle)；事实上，当我创建表时，我会裁剪
我正在尝试删除重复的行；没有办法；我一直收到错误(Oracle)；事实上，当我创建表时，我会裁剪
提问于 2022-07-13 19:33:26
create table salesmen
(
salesman_id int,
name varchar(30),
city varchar(30),
commission numeric(5)
)
insert into salesmen (salesman_id, name, city, commission)
values (5001, 'james hoog', 'new york', 0.15)
insert into salesmen (salesman_id, name, city, commission)
values (5002, 'nail knite', 'paris', 0.13)
insert into salesmen (salesman_id, name, city, commission)
values (5005, 'pit alex', 'london', 0.11)
insert into salesmen (salesman_id, name, city, commission)
values (5006, 'mc lyon', 'paris', 0.14)
insert into salesmen (salesman_id, name, city, commission)
values (5007, 'paul adam', 'rome', 0.13)
insert into salesmen (salesman_id, name, city, commission)
values (5003, 'lauson hen', 'san jose', 0.12);Delete aliasName
from
(Select
*,
ROW_NUMBER() over (Partition by salesman_id, name, city, commission order by salesman_id) as rowNumber
From
salesmen) aliasName
Where
rowNumber > 1输出: 5001詹姆斯胡格，纽约，05002钉knite，巴黎05005，坑亚历克斯伦敦05006 mc里昂巴黎05007保罗亚当罗马05003劳森母鸡圣何塞05003洛森圣何塞05006 mc里昂巴黎05001詹姆斯胡格纽约0#事实上，当我创建的桌子，我不止一次，所以我有重复的行。
回答 1
Stack Overflow用户
回答已采纳
发布于 2022-07-13 19:44:38
这是表的内容：
SQL> select * From salesmen order by 1;
SALESMAN_ID NAME CITY COMMISSION
----------- ------------------------------ ------------------------------ ----------
5001 james hoog new york 0
5002 nail knite paris 0
5003 lauson hen san jose 0
5005 pit alex london 0
5006 mc lyon paris 0
5007 paul adam rome 0
6 rows selected.
SQL>你说你想删除“重复”。哪些副本？我什么都没看到。
如果运行该delete语句(不过，当您使用星号引用所有列时，忽略了使用表别名)，则会得到以下错误：
SQL> delete
2 from
3 (
4 select
5 s.*, --> alias missing here
6 row_number()
7 over(partition by salesman_id, name, city, commission
8 order by
9 salesman_id
10 ) as rownumber
11 from
12 salesmen s --> alias added here
13 ) aliasname
14 where
15 rownumber > 1;
(
*
ERROR at line 3:
ORA-01732: data manipulation operation not legal on this view
SQL>是的，所以你不能这么做。
但是，无论如何，子查询返回什么？
SQL> select
2 s.*,
3 row_number()
4 over(partition by salesman_id, name, city, commission
5 order by
6 salesman_id
7 ) as rownumber
8 from
9 salesmen s;
SALESMAN_ID NAME CITY COMMISSION ROWNUMBER
----------- --------------- ---------- ---------- ----------
5001 james hoog new york 0 1
5002 nail knite paris 0 1
5003 lauson hen san jose 0 1
5005 pit alex london 0 1
5006 mc lyon paris 0 1
5007 paul adam rome 0 1
6 rows selected.
SQL>所有rownumber值都等于1 (由于partition by子句)，所以.您使用的条件:无论如何，where rownumber > 1不会做任何事情。
因此，你到底想做什么？在该示例表中您发现哪些行是“重复的”？
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/72971682
复制相关文章
相似问题
腾讯云开发者
