100分求: 怎样去掉表中一个时间的中间的数据?

[micrsoft3]

如下表

Table1
-----------------------------------
id name 时间
1 李 2006-10-10 09:00:00
2 李 2006-10-10 10:00:00
3 李 2006-10-10 18:00:00
4 李 2006-10-11 09:00:00
5 李 2006-10-11 18:00:00

上面的是一个考勤记录下,本来是上班打一次卡,下班打一次卡,可是如果有人打了三次以上的卡,则如何删除 中间时段的记录呢?

[jiewenzhai]

不清楚，帮顶

[oop80]

select * from
(select sum(yg), year(Time) y, month(Time) m from 表b
group by year(Time), month(Time)) a inner join

(select sum(sales), year(Time) y , month(Time) m from 表a
group by year(Time), month(Time)) b

on a.y=b.y and a.m=b.m

declare @tmp table
(
name varchar(100),
mintime datetime,
maxtime datetime
)

select name, min(时间), max(时间) into @tmp from table1
group by year(时间), month(时间), day(时间), name

delete from table1

insert into table1
(name, 时间)
select name, mintime
from @tmp

insert into table1
(name, 时间)
select name, maxtime
from @tmp

[oop80]

没经过测试，小心点用 ：）

[xiaoku]

--?
考勤记录中,不会存在倒班的情况的话,那就简单了很多!
delete from Table1
where right(convert(varchar(13),时间,120),) between '10' and '15'--剔除了早退和迟到的记录

其实你这个问题,好像也可以想得复杂点

[i9988]

如果一天只有2次，倒是可以的

delete a
from table1 a
where exists (
select 1 from table1
where name=a.name
and convert(char(10),时间,120)=convert(char(10),a.时间,120)
and 时间<a.时间
)
and exists (
select 1 from table1
where name=a.name
and convert(char(10),时间,120)=convert(char(10),a.时间,120)
and 时间>a.时间
)

[oop80]

--上面的多了点东西，这个是对的。

declare @tmp table
(
name varchar(100),
mintime datetime,
maxtime datetime
)

select name, min(时间), max(时间) into @tmp from table1
group by year(时间), month(时间), day(时间), name

delete from table1

insert into table1
(name, 时间)
select name, mintime
from @tmp

insert into table1
(name, 时间)
select name, maxtime
from @tmp

[hhhdyj]

delete Table1
where 时间 <> (select min(时间) from Table1 group by name, convert(varchar,时间,111)) and
时间 <> (select max(时间) from Table1 group by name, convert(varchar,时间,111))

[i9988]

上面只针对一天只要求打上下班两次的卡的

一般考勤是应该有排班表的，根据排班表的上下班时间，前后半小时或者一小时算有效打卡，有效打卡重复的上班取最早的，下班取最晚的

[hhhdyj]

不好意思，刚才写的有问题
declare @t table(id int, name varchar(10), time datetime)
insert into @t
select 1, '李' , '2006-10-10 09:00:00'
union all select 2, '李' , '2006-10-10 10:00:00'
union all select 3, '李' , '2006-10-10 18:00:00'
union all select 4, '李' , '2006-10-11 09:00:00'
union all select 5, '李' , '2006-10-11 18:00:00'

select max(time) from @t group by name, convert(varchar,time,111)
delete @t
from @t a
where time <> (select min(time) from @t where name = a.name and convert(varchar,time,111) = convert(varchar,a.time,111) group by name, convert(varchar,time,111))
and time <> (select max(time) from @t where name = a.name and convert(varchar,time,111) = convert(varchar,a.time,111) group by name, convert(varchar,time,111))

select * from @t

[Softlee81307]

Create Table Table1(id int,name varchar(10),time datetime)
Insert into table1
Select 1, '李', '2006-10-10 09:00:00' union all
Select 2, '李', '2006-10-10 10:00:00' union all
Select 3, '李', '2006-10-10 18:00:00' union all
Select 4, '李', '2006-10-11 09:00:00' union all
Select 5, '李', '2006-10-11 18:00:00'
--------------------删除---------------------------------------
Delete Table1 from table1 A inner join
(Select minid=min(id),maxid=max(id),name from table1 group by name) b on a.name=b.name
where a.id<>b.minid And a.id<>b.maxid
-----------------结果----------------------------------
select * from Table1
------------------------------------------------------
id name time
1 李 2006-10-10 09:00:00.000
5 李 2006-10-11 18:00:00.000

[xiaoku]

--我想的复杂了
--try
create table table1
(
id int IDENTITY( 1,1) not null ,
name varchar(10),
时间 datetime
)
insert into table1
select '李','2006-10-10 09:00:00' union all
select '李','2006-10-10 10:00:00' union all
select '李','2006-10-10 18:00:00' union all
select '李','2006-10-11 09:00:00' union all
select '李','2006-10-11 18:00:00'

select *from table1
--删除语句
delete
from table1
where 时间<(
select max(时间)
from table1
where name =table1.name
group by name,convert(varchar(10),时间,120)
having count(name)>=3
)
and 时间>(
select min(时间)
from table1
where name =table1.name
group by name,convert(varchar(10),时间,120)
having count(name)>=3
)

select *from table1
--结果
id name 时间
----------- ---------- ------------------------------------------------------
1 李 2006-10-10 09:00:00.000
3 李 2006-10-10 18:00:00.000
4 李 2006-10-11 09:00:00.000
5 李 2006-10-11 18:00:00.000

（所影响的行数为 4 行）

--

[taochunsong]

不是这样考虑的
考勤的话
至少还有一个排班的表

要根据那个表里面的数据进行计算的，一般不是删除数据的，

[dawugui]

其实应该这样,12点之前取最小的,12点之后取最大的.

declare @t table(id int, name varchar(10), time datetime)
insert into @t
select 1, '李' , '2006-10-10 09:00:00'
union all select 1, '李' , '2006-10-10 09:00:00'
union all select 2, '李' , '2006-10-10 10:00:00'
union all select 3, '李' , '2006-10-10 18:00:00'
union all select 4, '李' , '2006-10-11 09:00:00'
union all select 5, '李' , '2006-10-11 18:00:00'

select name , convert(varchar(10),time,120) as time , min(time) as '打卡记录' from @t where convert(varchar(10),time,114) < '12:00:00' group by name , convert(varchar(10),time,120)
union all
select name , convert(varchar(10),time,120) as time , max(time) as '打卡记录' from @t where convert(varchar(10),time,114) > '12:00:00' group by name , convert(varchar(10),time,120)
order by name , time

name time 打卡记录
---------- ---------- ------------------------------------------------------
李 2006-10-10 2006-10-10 09:00:00.000
李 2006-10-10 2006-10-10 18:00:00.000
李 2006-10-11 2006-10-11 09:00:00.000
李 2006-10-11 2006-10-11 18:00:00.000

（所影响的行数为 4 行）


select name , min(time) from @t where convert(varchar(10),time,114) < '12:00:00' group by name
union all
select name , max(time) from @t where convert(varchar(10),time,114) > '12:00:00' group by name
order by name

[micrsoft3]

没想到大家这么热情~

我主要是想这样,删除表中,当天每人打卡超过三次的,删除前一次和后一次中间的记录.

[dawugui]

上面后面一段是多余的.

其实应该这样,12点之前取最小的,12点之后取最大的.

declare @t table(id int, name varchar(10), time datetime)
insert into @t
select 1, '李' , '2006-10-10 09:00:00'
union all select 1, '李' , '2006-10-10 09:00:00'
union all select 2, '李' , '2006-10-10 10:00:00'
union all select 3, '李' , '2006-10-10 18:00:00'
union all select 4, '李' , '2006-10-11 09:00:00'
union all select 5, '李' , '2006-10-11 18:00:00'

select name , convert(varchar(10),time,120) as time , min(time) as '打卡记录' from @t where convert(varchar(10),time,114) < '12:00:00' group by name , convert(varchar(10),time,120)
union all
select name , convert(varchar(10),time,120) as time , max(time) as '打卡记录' from @t where convert(varchar(10),time,114) > '12:00:00' group by name , convert(varchar(10),time,120)
order by name , time

name time 打卡记录
---------- ---------- ------------------------------------------------------
李 2006-10-10 2006-10-10 09:00:00.000
李 2006-10-10 2006-10-10 18:00:00.000
李 2006-10-11 2006-10-11 09:00:00.000
李 2006-10-11 2006-10-11 18:00:00.000

（所影响的行数为 4 行）

[micrsoft3]

谢谢楼上的兄弟们!

问题解决了~!

分不够,dawugui(潇洒老乌龟) 来这里接分(不回答问题也接)

http://community.csdn.net/Expert/topic/5120/5120235.xml?temp=.3487512