50分请教SQL关于考勤的时间判断的问题

[falcon36017]

我做一个考勤系统
5分钟内的2次刷卡算一次

我怎么做这个语句

code,name,datetime

A0000,张三,2005-12-27 11:00:00
A0000,张三,2005-12-27 11:01:00
A0000,张三,2005-12-27 11:02:00
A0000,张三,2005-12-27 11:03:00
A0000,张三,2005-12-27 11:08:00

这样一个模型 应该得到的统计数为2。。。该怎么做！

[子陌红尘]

declare @t table(code varchar(10),name varchar(10),[datetime] datetime)
insert into @t select 'A0000','张三','2005-12-27 11:00:00'
insert into @t select 'A0000','张三','2005-12-27 11:01:00'
insert into @t select 'A0000','张三','2005-12-27 11:02:00'
insert into @t select 'A0000','张三','2005-12-27 11:03:00'
insert into @t select 'A0000','张三','2005-12-27 11:08:00'

declare @code varchar(10),@name varchar(10),@date datetime,@icnt int
set @code='A0000'
set @name='张三'
set @date='1900-01-01'
set @icnt=0

select
@icnt=@icnt+(case when datediff(mi,@date,[datetime])<5 then 0 else 1 end) ,
@date=(case when datediff(mi,@date,[datetime])<5 then @date else [datetime] end)
from
@t

select @icnt

[子陌红尘]

declare @t table(code varchar(10),name varchar(10),[datetime] datetime)
insert into @t select 'A0000','张三','2005-12-27 11:00:00'
insert into @t select 'A0000','张三','2005-12-27 11:01:00'
insert into @t select 'A0000','张三','2005-12-27 11:02:00'
insert into @t select 'A0000','张三','2005-12-27 11:03:00'
insert into @t select 'A0000','张三','2005-12-27 11:08:00'

declare @code varchar(10),@name varchar(10),@date datetime,@icnt int
set @code='A0000'
set @name='张三'
set @date='1900-01-01'
set @icnt=0

select
@icnt=@icnt+(case when datediff(mi,@date,[datetime])<5 then 0 else 1 end) ,
@date=(case when datediff(mi,@date,[datetime])<5 then @date else [datetime] end)
from
@t
where
code=@code and name=@name

select @icnt

[mislrb]

学习

[falcon36017]

感谢大师。。。先感激再仔细看代码

[junhao_666]

up

[justinfoo]

顶了

[LGame]

顶了