从Oracle SQL表中删除有限数量的行

Question

我希望删除每个在超过3个项目中工作的员工的确切两行/记录。假设我有以下表格：

+----------+-------------+
| employee |  Project    |
+----------+-------------+
|   1      |   p1        |
|   1      |   p2        |
|   1      |   p3        |
|   1      |   p4        |
|   2      |   p1        |
|   2      |   p3        |
|   3      |   p1        |
|   3      |   p4        |
|   3      |   p5        |
+----------+-------------+

我可以查询正在参与超过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;

重要的不是删除哪些行，而是删除每个参与三个以上项目的员工的两行/记录。 例如，得到的表格可能是：

+----------+-------------+
| employee |  Project    |
+----------+-------------+
|   1      |   p1        |
|   1      |   p2        |
|   2      |   p1        |
|   2      |   p3        |
|   3      |   p1        |
+----------+-------------+

- Youssef Khloufi

Answer 1

这个例子的简单方法是删除前1000行：

DELETE FROM YOUR_TABLE WHERE ROWID IN 
(SELECT ROWID FROM YOUR_TABLE FETCH FIRST 1000 ROWS ONLY);

- Piotr Rogowski

Answer 2

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        

- Dmitry Nikiforov