删除无效的重复的行SQL中 [英] Deleting Invalid Duplicate Rows in SQL

问题描述

我有一个存储通过时间机器员工的登记入住时间用户名的基础上的表。如果员工拳多次那么将是他签入的多个记录，因为这只会拥有几秒钟之间的时间差。显然，只有第一个记录是有效的。所有其它项是无效的，必须从表中删除。我如何能做到这一点，如果我可以选择一个员工的所有签记录当前的日期？

在数据库中的数据如下：

 用户名Checktime CheckType
 HRA001 7/29/2012上午08点16分44秒入住
 HRA001 7/29/2012上午08时16分46秒入住
 HRA001 7/29/2012上午8时16分五十秒入住
 HRA001 7/29/2012上午八点16分53秒入住
 

解决方案

试试这个：

 ;具有users_CTE为（
 选择在（由Checktime用户名为了分区）级（）从用户RNK
 ） DELETE FROM users_CTE哪里RNK＆LT;＆GT; 1
 

- 为了您的第二个要求尝试此查询

 ;具有users_CTE为（
 选择*，在（由Checktime用户名为了分区）级（）从用户RNK
 ）
，CTE 2为（选择用户名，MIN（CheckTime）为minTime，DATEADD（MI，1，MIN（CheckTime））作为MAXTIME从users_CTE
 通过用户名组）从用户删除不Checktime在（
从users_CTE C1选择c1.Checktime左连接CTE 2 C2
在c1.Checktime＆GT; c2.minTime和c1.Checktime＆LT; = c2.maxTime
其中，c2.Username不为空，并在c1.Username（从users_CTE C1选择c1.Username左连接CTE 2 C2
在c1.Checktime＆GT; c2.minTime和c1.Checktime＆LT; = c2.maxTime
通过c1.Username，c2.Username组
有COUNT（*）＆GT; 1））
 

- 为了您的改变要求请查看以下

该查询

  alter table将用户添加标志VARCHAR（2）;具有users_CTE为（
 选择*，在（由Checktime用户名为了分区）级（）从用户RNK
 ）
，CTE 2为（选择用户名，MIN（CheckTime）为minTime，DATEADD（MI，1，MIN（CheckTime））作为MAXTIME从users_CTE
 通过用户名组）
更新U盘u.flag ='D'从users_CTEû内部联接（
从users_CTE C1选择c1.Checktime左连接CTE 2 C2
在c1.Checktime＆GT; c2.minTime和c1.Checktime＆LT; = c2.maxTime
其中，c2.Username不为空，并在c1.Username（从users_CTE C1选择c1.Username左连接CTE 2 C2
在c1.Checktime＆GT; c2.minTime和c1.Checktime＆LT; = c2.maxTime
通过c1.Username，c2.Username组
有COUNT（*）＆GT; 1））一
在u.Checktime = a.Checktime
 

- 检查最新的查询与DeletFlag

 ;具有users_CTE作为
（
 选择*，ROW_NUMBER（）OVER（由Checktime用户名为了分区）从用户的行
）
，CTE为（
选择行，用户名，Checktime，CheckType，0作为totalSeconds，'N'作为Delflag从users_CTE其中row = 1
UNION ALL
选择t.row，t.Username，t.Checktime，t.CheckType，CASE WHEN（c.totalSeconds + DATEDIFF（SECOND，c.Checktime，t.Checktime））＆GT; = 60则0，否则，（c.totalSeconds + DATEDIFF （SECOND，c.Checktime，t.Checktime））结束的totalSeconds，
CASE WHEN（c.totalSeconds + DATEDIFF（SECOND，c.Checktime，t.Checktime））＆GT; = 60，然后'N'别人'Y'端为Delflag
--CASE WHEN c.totalSeconds＆LT; = 60，然后'Y'别人'N'端为Delflag
从users_CTEŧ内部联接CTEÇ
在t.row = c.row + 1
）从CTE中选择用户名，Checktime，CheckType，Delflag
 

I have a table which stores the check-in times of employees through Time Machine on the basis of a username. If an employee punches multiple times then there would be multiple records of his check-ins which would only have a time difference of few seconds in between. Obviously only the first record is valid. All the other entries are invalid and must be deleted from the Table. How can i do it if i can select all the checkin records of an employee for the current date?

The Data in the db is as follows.

Username               Checktime                       CheckType
 HRA001            7/29/2012 8:16:44 AM                Check-In
 HRA001            7/29/2012 8:16:46 AM                Check-In
 HRA001            7/29/2012 8:16:50 AM                Check-In 
 HRA001            7/29/2012 8:16:53 AM                Check-In 

解决方案

Try this:

 ;WITH users_CTE as (
 select rank() over (partition by Username order by Checktime) as rnk from users
 )

 DELETE FROM users_CTE where rnk <> 1

--For your second requirement try this query

 ;WITH users_CTE as (
 select *,rank() over (partition by Username order by Checktime) as rnk from users
 )
,CTE2 as (select Username,MIN(CheckTime) as minTime,DATEADD(mi,1,MIN(CheckTime)) as maxTime from users_CTE 
 group by Username)



delete from users where Checktime in(
select c1.Checktime from users_CTE c1 left join CTE2 c2
on c1.Checktime > c2.minTime and c1.Checktime <= c2.maxTime
where c2.Username is not null and c1.Username in(

select c1.Username from users_CTE c1 left join CTE2 c2
on c1.Checktime > c2.minTime and c1.Checktime <= c2.maxTime
group by c1.Username,c2.Username 
having COUNT(*) > 1))

--For your changed requirements pls check this query below

alter table users add flag varchar(2)

;WITH users_CTE as (
 select *,rank() over (partition by Username order by Checktime) as rnk from users
 )
,CTE2 as (select Username,MIN(CheckTime) as minTime,DATEADD(mi,1,MIN(CheckTime)) as maxTime from users_CTE 
 group by Username)


update u SET u.flag = 'd' from users_CTE u inner join (
select c1.Checktime from users_CTE c1 left join CTE2 c2
on c1.Checktime > c2.minTime and c1.Checktime <= c2.maxTime
where c2.Username is not null and c1.Username in(

select c1.Username from users_CTE c1 left join CTE2 c2
on c1.Checktime > c2.minTime and c1.Checktime <= c2.maxTime
group by c1.Username,c2.Username 
having COUNT(*) > 1)) a
on u.Checktime=a.Checktime

--Check the latest query with DeletFlag

;WITH users_CTE as 
(
 select *,row_number() over (partition by Username order by Checktime) as row from users
)
,CTE as(
select row,Username,Checktime,CheckType,0 as totalSeconds,'N' as Delflag from users_CTE where row=1 
union all
select t.row,t.Username,t.Checktime,t.CheckType,CASE WHEN (c.totalSeconds + DATEDIFF(SECOND,c.Checktime,t.Checktime))  >= 60 then 0 else (c.totalSeconds + DATEDIFF(SECOND,c.Checktime,t.Checktime)) end as totalSeconds,
CASE WHEN (c.totalSeconds + DATEDIFF(SECOND,c.Checktime,t.Checktime))  >= 60 then 'N' else 'Y' end as Delflag
--CASE WHEN c.totalSeconds <= 60  then 'Y' else 'N' end as Delflag
from users_CTE t inner join CTE c
on t.row=c.row+1
)

select Username,Checktime,CheckType,Delflag from CTE

这篇关于删除无效的重复的行SQL中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！