删除sql数据库中的重复行

Question

我有一个包含以下字段的表：

`contract`, `title`, `category`, `reglementation`, `company`, `role`, `start_date`, `end_date`, `creation_date`, `update_date`, `created_by`, `updated_by`,`context`,`hash`,`accept_schedule`,`need_timecard`

并且，我需要删除具有相同约定的行，并且end_date为null，但具有较小的start_date 值

该查询返回重复的行，返回9000

select contract, count(*) 
from n_h_associate_occupation o 
where end_date is null 
group by o.contract 
having count(*) > 1;

有办法删除那些行吗？PLease帮助

Answer 1

这里有一个选项应该适用于所有版本的MySQL：

SELECT o1.*
FROM n_h_associate_occupation o1
INNER JOIN
(
SELECT contract, MIN(start_date) AS min_start_date
FROM n_h_associate_occupation
WHERE end_date IS NULL
GROUP BY contract
) o2
ON o1.contract = o2.contract AND
o1.start_date = o2.min_start_date
WHERE
o1.end_date IS NULL;

这假设您希望保留开始日期最小的行，如果给定的合同有多行。

Answer 2

如果对于contract的每个值，您希望去掉所有具有最小start_date值的行，即只保留具有最大start_date的行，对于end_date为null的行，则：

delete o from
n_h_associate_occupation o join
( 
select contract, max(start_date) as max_start_date
from n_h_associate_occupation
where end_date is null
group by contract
having count(*) > 1
) sq
on o.contract = sq.contract and o.end_date is null and o.start_date <> sq.max_start_date

参见Db Fiddle

如果您想保留最小start_date的行(从您的问题中还不完全清楚(，那么：

delete o from
n_h_associate_occupation o join
( 
select contract, min(start_date) as min_start_date
from n_h_associate_occupation
where end_date is null
group by contract
having count(*) > 1
) sq
on o.contract = sq.contract and o.end_date is null and o.start_date <> sq.min_start_date