|
, 304)
327 GO
328 print 'Processed 300 total records'
329 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x073C0B00 AS Date), 305)
330 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x083C0B00 AS Date), 306)
331 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x093C0B00 AS Date), 307)
332 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0A3C0B00 AS Date), 308)
333 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0B3C0B00 AS Date), 309)
334 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0C3C0B00 AS Date), 310)
335 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0D3C0B00 AS Date), 311)
336 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0E3C0B00 AS Date), 312)
337 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0F3C0B00 AS Date), 313)
338 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x103C0B00 AS Date), 314)
339 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x113C0B00 AS Date), 315)
340 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x123C0B00 AS Date), 316)
341 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x133C0B00 AS Date), 317)
342 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x143C0B00 AS Date), 318)
343 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x153C0B00 AS Date), 319)
344 SET IDENTITY_INSERT [dbo].[liu_shui] OFF
345
346
347 --输入任何一个日期，查找其之前和之后的连续的日期序列
348 create function Func_Continuity(@d_start_date date)--+1时点之后的日期序列，-1时点之前的日期序列
349 returns table
350 as
351 return
352 (
353 with cte_end
354 as
355 (
356 select i_id,d_date
357 from dbo.liu_shui
358 where d_date=@d_start_date
359 union all
360 select a.i_id,a.d_date
361 from dbo.liu_shui a
362 inner join cte_end as b
363 on DATEDIFF(day,b.d_date,a.d_date)=1--时点之后的日期序列
364 )
365 ,cte_begin
366 as
367 (
368 select i_id,d_date
369 from dbo.liu_shui
370 where d_date=@d_start_date
371 union all
372 select a.i_id,a.d_date
373 from dbo.liu_shui a
374 inner join cte_begin as b
375 on DATEDIFF(day,b.d_date,a.d_date)=-1--时点之前的日期序列
376 )
377 select * from cte_begin
378 union
379 select * from cte_end
380 )
381 go
382
383
384 select i_id,d_date from
385 (
386 select a.i_id,a.d_date,b.d_date as b_d_date from dbo.liu_shui as a
387 cross apply Func_Continuity(a.d_date) as b
388 ) as tt
389 group by i_id,d_date
390 having COUNT(b_d_date)>=3--大于等于几就是连续多少天及以上发生
391 order by d_date
?
|