我希望删除每个在超过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 |
+----------+-------------+