如何去除SQL数据表中的重复值？解决查询结果重复问题

问题根源 #

你的SQL出现重复数据的核心原因是使用了**CROSS JOIN**，它会让t1表的每一行和tt子查询的每一行进行全量匹配，产生笛卡尔积，导致大量重复记录。此外UNION ALL会保留子查询内部的重复行，进一步加重问题。

修改后的查询代码 #

第一步：修正关联逻辑，避免笛卡尔积 #

将交叉连接改为按日期关联的左连接，同时修正子查询的重复问题：

SELECT DISTINCT 
    COALESCE(tt.ID, t1.ID) AS ID,
    COALESCE(tt.Name, t1.Name) AS Name,
    COALESCE(tt.Class, t1.Class) AS Class,
    COALESCE(tt.Date, t1.Date) AS Date,
    COALESCE(tt.Intime, t1.Intime) AS Intime,
    COALESCE(tt.Outtime, t1.Outtime) AS Outtime,
    COALESCE(tt.INAM, t1.INAM) AS INAM,
    COALESCE(tt.OUTPM, t1.OUTPM) AS OUTPM
FROM stuattrecordAMPM AS t1
LEFT JOIN (
    -- 用UNION替代UNION ALL，自动去除子查询内部重复
    SELECT * FROM stuattrecordAMPM
    UNION
    SELECT null, null, null, Date, Holiday_Name, Holiday_Name, Status, Status FROM HolidayList
) AS tt ON t1.Date = tt.Date  -- 按日期关联，避免全量交叉
ORDER BY ID;

第二步：单独执行删除语句 #

原代码中SELECT和DELETE写在一起存在语法错误，需分开执行删除操作：

DELETE FROM stuattrecordAMPM
WHERE Date IS NULL;

进阶方案：用MERGE避免插入重复数据 #

如果你的目标是将假期数据合并到stuattrecordAMPM表且避免重复插入，可以使用MERGE语句（适用于SQL Server、Oracle等支持的数据库），它会自动判断记录是否存在，只插入不存在的行：

MERGE INTO stuattrecordAMPM AS target
USING (
    SELECT DISTINCT 
        ID, Name, Class, Date, Intime, Outtime, INAM, OUTPM
    FROM (
        SELECT ID, Name, Class, Date, Intime, Outtime, INAM, OUTPM FROM stuattrecordAMPM
        UNION
        SELECT null, null, null, Date, Holiday_Name, Holiday_Name, Status, Status FROM HolidayList
    ) AS combined
) AS source
-- 按ID和Date判断记录是否重复
ON target.ID = source.ID AND target.Date = source.Date
WHEN NOT MATCHED THEN
    INSERT (ID, Name, Class, Date, Intime, Outtime, INAM, OUTPM)
    VALUES (source.ID, source.Name, source.Class, source.Date, source.Intime, source.Outtime, source.INAM, source.OUTPM);

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