删除除部分行之外的所有行 - Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1808669/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete all but some rows - Oracle
提问by Guru
I want to delete set of the rows from a table. I can decide which rows need to be deleted. I will delete the rows from table only if the count of rows is more than 5 (based on condition).
我想从表中删除一组行。我可以决定需要删除哪些行。仅当行数超过 5（基于条件）时，我才会从表中删除行。
Consider this sample data
考虑这个样本数据
ID--Col1--Col2--
1 A X
2 A X
3 A X
4 A X
5 A X
6 A X
7 A X
8 A X
9 A X
10 A X
11 B X
12 B X
13 B X
14 B X
15 C X
16 C X
17 C X
18 D X
19 D X
I want to delete 5 Rows of {*, A, X}, I need to keep 5 of them (no matter which one). I wont delete B, C & D since count of them is less then 5.
我想删除 {*, A, X} 的 5 行，我需要保留其中的 5 行（无论是哪一个）。我不会删除 B、C 和 D，因为它们的数量少于 5。
Like
喜欢
delete from tableA
--- I can decide on the rows to delete based on two conditions.
where col1 = someCondition
and col2 = someOtherCondition
and rownum > 5 --- This dint work. I checked.
I think perhaps I need to programmaticaly. Any suggestions are greatly appreciated.
我想也许我需要以编程方式。任何建议都非常感谢。
回答by David Aldridge
This will delete all rows for each unique combination of col1 and col2 other than the first five ordered by rowid
这将删除 col1 和 col2 的每个唯一组合的所有行，而不是按 rowid 排序的前五行
delete from my_table
where rowid in
(
select rowid
from
(
select rowid,
row_number() over (partition by col1, col2 order by rowid) rownumber
from my_table
)
where rownumber > 5
)
/
回答by Vincent Malgrat
this will delete all rows except 20 rows that satisfy both conditions on Col1 and Col2:
这将删除除了满足 Col1 和 Col2 两个条件的 20 行之外的所有行：
DELETE FROM tableA
WHERE ROWID NOT IN (SELECT ROWID
FROM tableA
WHERE col1 = someCondition
AND col2 = someOtherCondition
AND rownum <= 20)
If your data set is really large, the following may be faster:
如果您的数据集非常大，以下可能会更快：
CREATE tableTemp as
SELECT *
FROM tableA
WHERE col1 = someCondition
AND col2 = someOtherCondition
AND rownum <= 20;
TRUNCATE tableA;
INSERT INTO tableA (SELECT * FROM tableTemp);
Replace the truncate by a DELETE if you need to access the data during the operation.
如果您需要在操作期间访问数据，请用 DELETE 替换截断。
回答by Tony Andrews
This will keep a maximum of 5 from each group:
这将使每组最多保留 5 个：
delete mytable where rowid in
( select rowid from
( select rowid, row_number() over (partition by col1, col2 order by id) rn
from mytable
)
where rn > 5
);
回答by Erich Kitzmueller
ROWNUM>5 doesn't work because for the first affected row, ROWNUM is always 1; this condition also includes the "ROWNUM>5" part, so Oracle can't possibly find a row that fits the bill.
ROWNUM>5 不起作用，因为对于第一个受影响的行，ROWNUM 始终为 1；此条件还包括“ROWNUM>5”部分，因此 Oracle 不可能找到符合要求的行。
This might work:
这可能有效：
delete from tableA
--- I can decide on the rows to delete based on two conditions.
where col1 = someCondition
and col2 = someOtherCondition
and rownum <= (select count(*) from tableA
where col1 = someCondition
and col2 = someOtherCondition) - 5;
回答by Inder
To keep ANY 500 rows in a table and delete the rest.
将任意 500 行保留在表中并删除其余行。
delete from tablenameX where rownum <= (select count(*) - 500 from tablenamex);
Above sql is only advisable if row count is small. If large then use the approach suggested earlier of creating a temporary table.
以上 sql 仅在行数较小时才可取。如果大，则使用之前建议的创建临时表的方法。
