mysql查询连续三天的数据,该如何优化查询sql

mysql查询连续三天的数据,该如何优化查询sql,第1张

SELECT * FROM `curriculum` where cuc_class_date >'2016-10-01' and cuc_class_date <= date_add('2016-10-01', INTERVAL 3 day)

这样可能会把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


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/sjk/6648628.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-03-26
下一篇 2023-03-26

发表评论

登录后才能评论

评论列表(0条)

保存