如何使用删除sql server数据库中的重复行？

Question

​

​

我有一个考勤系统，有日期、签到、结账等列，用户上班打卡，外出打卡。系统从指纹扫描仪机器检索数据两次。我想删除在同一日期超过一个计数的行，打卡时间在上午7点到上午11点之间，结帐的行在上午11点到下午6点之间。

    SELECT
    a.Logid, 
    a.Userid, 
    a.CheckTime,  
    a.Name  

FROM Checkinout a 
JOIN
(SELECT
    userid,
    name,  
    dateinout,  
    Intime,
    Outtime

    FROM att
    WHERE Intime BETWEEN '07:00:00.0000000' AND '11:00:00.0000000'
    AND userid= 37
    GROUP BY userid, dateinout, Intime, Outtime, name
    HAVING COUNT(Intime)>1) b
    ON a.Userid= b.userid
    ORDER BY CheckTime ASC;

Answer 1

您可以使用cte根据您的分组条件从att表中删除重复项。

;WITH CTE AS(
SELECT
row_number() over (partition by  userid,dateinout, Intime, Outtime order by date) AS ROWNUMBER,
        userid,
        dateinout,  
        Intime,
        Outtime
    FROM 
        att
    WHERE 
        Intime BETWEEN '07:00:00.0000000' AND '11:00:00.0000000'
        AND userid = 37
 )

 DELETE FROM CTE WHERE ROWNUMBER>1

Answer 2

你可以一步一步来做。首先，在时间范围内找到插入计数：

SELECT COUNT(Logid), userid
FROM Checkinout
WHERE Intime BETWEEN '07:00:00.0000000' AND '11:00:00.0000000'
GROUP BY Logid
HAVING COUNT(Logid) > 1

之后，你得到了列表，你可以通过你需要的属性来分散注意力