这样可能会把10月1号非零点的也查出来,如果不想查出来在加date_format()函数格式一下时间
/*求连续旷工三天以上的数据*/declare @t table(name varchar(10), [date] datetime,n int default(1))
insert into @t(name,date) select '张三','2011.09.01'
union select '张三','2011.09.08'
union select '张三','2011.09.09'
union select '张三','2011.09.10'
union select '李四','2011.09.06'
union select '李四','2011.09.09'
union select '李四','2011.09.12'
union select '李四','2011.09.15'
union select '小五','2011.08.06'
select * from @t
--select name,COUNT(*) 次数
-- from @t group by name having(COUNT(*)>3)
declare @nm varchar(10),@d datetime,@n int=1,@lastNm varchar(10)='',@lastD datetime='1900.01.01',@lastN int =1
declare cur cursor for select name ,[date],n from @t order by name,date
open cur
fetch next from cur into @nm,@d,@n
while (@@FETCH_STATUS =0 ) begin
if @lastNm =@nm and @d=@lastD+1 begin
update @t set n = @lastN +1 where name=@nm and [date]=@d
select @lastN = n from @t where name=@nm and [date]=@d
set @lastNm=@nm
set @lastD =@d
end
else begin
set @lastNm = @nm
set @lastD =@d
set @lastN = @n
end
fetch next from cur into @nm,@d,@n
end
close cur
deallocate cur
select * from @t where n>=3
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)