如何删除EmployeeWorkingDay重复行并保留与Schedule表有约束的行？

需要删除EmployeeWorkingDay表中的重复行，仅保留那些与Schedule表存在外键约束关联的行（同一分组下可能有多行都存在关联）。

已使用以下SQL脚本查询重复数据：

SELECT 
    E.ID, 
    E.EmployeeId, 
    E.DailyWorkingTemplateId, 
    E.EMployeeWorkingWeekId, 
    T.rank
    --,s.* --(Schedule table)
FROM EMployeeWorkingDay E
  INNER JOIN
  (
     SELECT *, 
            RANK() OVER(PARTITION BY EmployeeId, 
                                     DailyWorkingTemplateId, 
                                     EMployeeWorkingWeekId
            ORDER BY id) rank
     FROM EmployeeWorkingDay 
    ) T ON E.ID = t.ID

--inner join Schedule s on E.Id = s.EmployeeWorkingDayId (Table Schedule has FK column on table EmployeeWorkingDay)

执行后得到精简数据集：

ID     EmployeeId   DailyWorkingTemplateId  EMployeeWorkingWeekId   rank
=====  ==========  ======================== =====================  ======
29847   3721        1240                    6836                     1
29848   3721        1240                    6836                     2
29850   3721        1240                    6836                     3
29853   3721        1240                    6837                     1
29854   3721        1240                    6837                     2
29855   3721        1240                    6837                     3
29856   3721        1240                    6837                     4
29857   3721        1240                    6837                     5

已知可以删除rank>1的行，但无法确定哪些行与Schedule表存在外键关联，尝试用左连接但遇到困难，想知道如何在删除前检查这些关联关系。

1. 先检查哪些行存在外键关联 #

通过左连接+状态标记，直观查看每一行是否和Schedule表有关联：

SELECT 
    E.ID, 
    E.EmployeeId, 
    E.DailyWorkingTemplateId, 
    E.EMployeeWorkingWeekId, 
    T.rank,
    CASE WHEN s.EmployeeWorkingDayId IS NOT NULL THEN '存在关联' ELSE '无关联' END AS schedule关联状态
FROM EMployeeWorkingDay E
INNER JOIN (
    SELECT *, 
           RANK() OVER(PARTITION BY EmployeeId, DailyWorkingTemplateId, EMployeeWorkingWeekId ORDER BY id) rank
    FROM EmployeeWorkingDay 
) T ON E.ID = t.ID
LEFT JOIN Schedule s ON E.Id = s.EmployeeWorkingDayId
ORDER BY E.EmployeeWorkingWeekId, T.rank;

2. 筛选出需要保留的行 #

如果要保留同一分组（EmployeeId+DailyWorkingTemplateId+EMployeeWorkingWeekId）中所有存在关联的行，可先查询出需要保留的ID集合：

WITH working_day_ranked AS (
    SELECT 
        ID,
        EmployeeId,
        DailyWorkingTemplateId,
        EMployeeWorkingWeekId,
        EXISTS (SELECT 1 FROM Schedule s WHERE s.EmployeeWorkingDayId = ID) AS has_schedule关联
    FROM EmployeeWorkingDay
)
SELECT ID FROM working_day_ranked
WHERE has_schedule关联 = 1
-- 可选逻辑：如果分组中没有任何关联行，保留该组rank=1的行
-- UNION
-- SELECT ID FROM (
--     SELECT 
--         ID,
--         RANK() OVER(PARTITION BY EmployeeId, DailyWorkingTemplateId, EMployeeWorkingWeekId ORDER BY id) rank
--     FROM working_day_ranked
--     WHERE has_schedule关联 = 0
-- ) t WHERE rank = 1

3. 删除重复行（谨慎操作） #

确认保留的行后，执行删除，只删除不在保留集合中的行：

WITH working_day_ranked AS (
    SELECT 
        ID,
        EmployeeId,
        DailyWorkingTemplateId,
        EMployeeWorkingWeekId,
        EXISTS (SELECT 1 FROM Schedule s WHERE s.EmployeeWorkingDayId = ID) AS has_schedule关联
    FROM EmployeeWorkingDay
),
keep_ids AS (
    SELECT ID FROM working_day_ranked
    WHERE has_schedule关联 = 1
    -- 可选：补充无关联分组保留首行的逻辑
    UNION
    SELECT ID FROM (
        SELECT 
            ID,
            RANK() OVER(PARTITION BY EmployeeId, DailyWorkingTemplateId, EMployeeWorkingWeekId ORDER BY id) rank
        FROM working_day_ranked
        WHERE has_schedule关联 = 0
    ) t WHERE rank = 1
)
DELETE FROM EmployeeWorkingDay
WHERE ID NOT IN (SELECT ID FROM keep_ids);

注意事项 #

• 执行删除前必须先备份数据，或者用SELECT * FROM EmployeeWorkingDay WHERE ID NOT IN (SELECT ID FROM keep_ids)预览要删除的行，确认无误后再执行删除。
• 不同数据库语法略有差异：比如SQL Server需要写成DELETE E FROM EmployeeWorkingDay E WHERE E.ID NOT IN (...)；MySQL 8.0+才支持CTE，低版本可用子查询替代。

内容的提问来源于stack exchange，提问作者lonelydev101