基于字段值在Access中选择性删除表中的重复记录

解决方案 #

可以通过SQL实现该需求，以下针对主流数据库给出具体语句，执行前建议先替换实际表名，并通过SELECT语句验证待删除记录：

MySQL/MariaDB

DELETE t1
FROM your_table t1
LEFT JOIN (
    SELECT 
        Email, 
        Course,
        -- 优先保留DateComplete非空的记录ID，无则取最小ID
        CASE WHEN COUNT(DateComplete) > 0 THEN MAX(CASE WHEN DateComplete IS NOT NULL THEN ID END)
             ELSE MIN(ID)
        END AS keep_id
    FROM your_table
    GROUP BY Email, Course
) t2 ON t1.ID = t2.keep_id
WHERE t2.keep_id IS NULL;

SQL Server

WITH ranked_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Email, Course 
            ORDER BY CASE WHEN DateComplete IS NOT NULL THEN 0 ELSE 1 END, ID
        ) AS rn
    FROM your_table
)
DELETE FROM ranked_records
WHERE rn > 1;

PostgreSQL

WITH ranked_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Email, Course 
            ORDER BY CASE WHEN DateComplete IS NOT NULL THEN 0 ELSE 1 END, id
        ) AS rn
    FROM your_table
)
DELETE FROM your_table
WHERE id IN (SELECT id FROM ranked_records WHERE rn > 1);

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