oracle 从oracle sql表中删除有限的n行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22897374/
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 limited n rows from oracle sql table
提问by Youssef Khloufi
I want to delete exactly 2 rows/records for each employee that is working in more than 3 projects. Let's say I have this table:
我想为在 3 个以上项目中工作的每个员工准确删除 2 行/记录。假设我有这张桌子：
+----------+-------------+
| employee | Project |
+----------+-------------+
| 1 | p1 |
| 1 | p2 |
| 1 | p3 |
| 1 | p4 |
| 2 | p1 |
| 2 | p3 |
| 3 | p1 |
| 3 | p4 |
| 3 | p5 |
+----------+-------------+
I can query which employees are working in more than 3 projects. In this case the employee with id 1 and the employee with id 3. The query should be:
我可以查询哪些员工在 3 个以上的项目中工作。在这种情况下，id 为 1 的员工和 id 为 3 的员工。查询应该是：
select employee
from (
select employee, count(*) my_count
from my_table
group by employee
) VW
where VW.my_count >= 3;
It is not important which rows to delete, what is relevant is to delete two rows/records for every employee that works in more than three projects. The resulting table could be for example:
删除哪些行并不重要，重要的是为每个在三个以上项目中工作的员工删除两行/记录。结果表可以是例如：
+----------+-------------+
| employee | Project |
+----------+-------------+
| 1 | p1 |
| 1 | p2 |
| 2 | p1 |
| 2 | p3 |
| 3 | p1 |
+----------+-------------+
回答by Piotr R
It is simple approach for this example remove 1000 first rows:
这个例子的简单方法是删除 1000 第一行：
DELETE FROM YOUR_TABLE WHERE ROWID IN
(SELECT ROWID FROM YOUR_TABLE FETCH FIRST 1000 ROWS ONLY);
回答by Dmitry Nikiforov
SQL> select * from t;
EMPLOYEE PR
---------- --
1 p1
1 p2
1 p3
1 p4
2 p1
2 p3
3 p1
3 p4
3 p5
SQL> delete from t
2 where rowid in (
3 select rid from (
4 select rowid rid,
5 row_number() over(partition by employee
6 order by project desc) rn,
7 count(*) over(partition by employee) cnt
8 from t
9 ) where cnt >= 3 and rn <=2
10 )
11 /
4 rows deleted.
SQL> select * from t;
EMPLOYEE PR
---------- --
1 p1
1 p2
2 p1
2 p3
3 p1
