我有一个存储通过时间机器员工的登记入住时间用户名的基础上的表。 如果员工拳多次话就不会有他签入的多个记录，因为这只会有几秒钟之间的时间差。 显然，只有第一个记录是有效的。 所有其他入口是无效的，必须从表中删除。 我该怎么办，如果我可以选择一个员工的所有签记录当前日期？
在DB中的数据如下所示。
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
尝试这个：
;WITH users_CTE as (
select rank() over (partition by Username order by Checktime) as rnk from users
)
DELETE FROM users_CTE where rnk <> 1
──为你的第二个要求尝试此查询
;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))
──为你改变的需求请看看下面这个查询
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
- 查看与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
你为什么不将它们插入到数据库之前验证签入。 如果存在任何支票，为此用户，此日期和该日期间，然后别的什么也不做插入
您应该能够通过时间订购的所有记录，从每名员工先前时间减去最新的时间，如果结果低于某一阈值，与最近一次删除的行（或多个）。
你可以尝试，并通过签时间排名记录，然后删除每个员工的所有记录的每一天具有RANK大于1。
尝试此查询：从雇员其中employee.checkin删除（从雇员选择签入其中计数（签）> 1）;
http://codesimplified.com/2010/10/18/remove-duplicate-records-from-the-database-table/
希望这将帮助你。
DELETE FROM timesheet
WHERE timesheetRecordId <>(
SELECT TOP 1 timesheetRecordId from timesheet
WHERE checkInDate=todaysDate AND employeeId=empId ORDER BY checkInTime ASC
)
AND checkInDate=today's date AND empolyeeId=empId;
I don't think you can specify a Target Table, from a Delete statement, in a Subquery of that same statement. So you can't do it with one single Delete statement.
What you can do is write a stored procedure. In your Stored Procedure you should create a Temporary Table containing the PKs returned by this Query:
select cht.pkey
from CheckTimeTable as cht
where exists ( select pkey
from CheckTimeTable
where username = cht.userName
and checkType = 'check-IN'
and Checktime >= subtime(cht.Checktime, '0 0:0:15.000000')
and Checktime < cht.Checktime);
Then write another statement to delete those PKs from your original table, CheckTimeTable.
Note that the query above is for MySQL, so you'll need to find the way to subtract 15 seconds from a timestamp for your DBMS. In MySQL it's done like this:
subtime(cht.Checktime, '0 0:0:15.000000')
This query will return whichever CheckTime record that has another CheckTime record from the same user, with the type Check-In, and within 15 seconds earlier than its own checktime.
