如何判断连续5天以上某个字段为0

Question

Date BssID BtsID ERL
2009-10-14 00:00:00 1 720 0
2009-10-15 00:00:00 1 720 2729
2009-10-16 00:00:00 1 720 44807
2009-10-17 00:00:00 1 720 0
2009-10-18 00:00:00 1 720 0
2009-10-19 00:00:00 1 720 0
2009-10-20 00:00:00 1 720 0
2009-10-21 00:00:00 1 720 0
2009-10-22 00:00:00 1 720 67276如何判断连续5天以上ERL字段为0，提取出最后一条记录

Answer 1

--> 生成测试数据: @tb
DECLARE @tb TABLE (Date datetime,BssID int,BtsID int,ERL int)
INSERT INTO @tb
SELECT '2009-10-14 00:00:00',1,720,0 UNION ALL
SELECT '2009-10-15 00:00:00',1,720,2729 UNION ALL
SELECT '2009-10-16 00:00:00',1,720,44807 UNION ALL
SELECT '2009-10-17 00:00:00',1,720,0 UNION ALL
SELECT '2009-10-18 00:00:00',1,720,0 UNION ALL
SELECT '2009-10-19 00:00:00',1,720,0 UNION ALL
SELECT '2009-10-20 00:00:00',1,720,0 UNION ALL
SELECT '2009-10-21 00:00:00',1,720,0 UNION ALL
SELECT '2009-10-22 00:00:00',1,720,67276--SQL查询如下:SELECT * FROM @tb AS A
WHERE EXISTS(SELECT 1
             FROM @tb WHERE Date >= DATEADD(day,-5,A.Date) AND Date < A.Date
                 AND ERL = 0
             GROUP BY ERL
             HAVING COUNT(*)>=5)

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-10-23 15:14:31
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([Date] datetime,[BssID] int,[BtsID] int,[ERL] int)
insert [tb]
select '2009-10-14 00:00:00',1,720,0 union all
select '2009-10-15 00:00:00',1,720,2729 union all
select '2009-10-16 00:00:00',1,720,44807 union all
select '2009-10-17 00:00:00',1,720,0 union all
select '2009-10-18 00:00:00',1,720,0 union all
select '2009-10-19 00:00:00',1,720,0 union all
select '2009-10-20 00:00:00',1,720,0 union all
select '2009-10-21 00:00:00',1,720,0 union all
select '2009-10-22 00:00:00',1,720,67276
--------------开始查询--------------------------
select id=identity(int,1,1),* into # from tb
select Date,BssID,BtsID,ERL
 from 
(
select 
  [Date],[BssID],[BtsID],[ERL],col5=(
   select 
     count(1) 
   from 
      # 
   where 
     [ERL]=a.[ERL] and id<=a.id 
   and 
     id>=(select isnull(max(id),0) from # where id<a.id and [ERL]!=a.[ERL]))
from 
   # a
)t
where 
 col5>=5
drop table #
----------------结果----------------------------
/* Date                    BssID       BtsID       ERL
----------------------- ----------- ----------- -----------
2009-10-21 00:00:00.000 1           720         0(1 行受影响)
*/

Answer 3

declare @t table(Date datetime,BssID int,BtsID int,ERL int)
insert @t values('2009-10-14 00:00:00',1,720,0 )
insert @t values('2009-10-15 00:00:00',1,720,2729 )
insert @t values('2009-10-16 00:00:00',1,720,44807 )
insert @t values('2009-10-17 00:00:00',1,720,0 )
insert @t values('2009-10-18 00:00:00',1,720,0 )
insert @t values('2009-10-19 00:00:00',1,720,0 )
insert @t values('2009-10-20 00:00:00',1,720,0 )
insert @t values('2009-10-21 00:00:00',1,720,0 )
insert @t values('2009-10-22 00:00:00',1,720,67276 )
select *
from @t a
where 
(select count(1) from @t 
where date between a.date-4 and a.date 
and ERL=0 )>=5
/*
Date                    BssID       BtsID       ERL
----------------------- ----------- ----------- -----------
2009-10-21 00:00:00.000 1           720         0
*/

Answer 4

---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2009-10-23 15:16:32
---------------------------------
 
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
go
CREATE TABLE [tb] (Date DATETIME,BssID INT,BtsID INT,ERL INT)
INSERT INTO [tb]
SELECT '2009-10-14 00:00:00',1,720,0 UNION ALL
SELECT '2009-10-15 00:00:00',1,720,2729 UNION ALL
SELECT '2009-10-16 00:00:00',1,720,44807 UNION ALL
SELECT '2009-10-17 00:00:00',1,720,0 UNION ALL
SELECT '2009-10-18 00:00:00',1,720,0 UNION ALL
SELECT '2009-10-19 00:00:00',1,720,0 UNION ALL
SELECT '2009-10-20 00:00:00',1,720,0 UNION ALL
SELECT '2009-10-21 00:00:00',1,720,0 UNION ALL
SELECT '2009-10-22 00:00:00',1,720,67276select * from [tb]
;with 
wang as (select s.* ,cnt=1 from tb s left join tb t on s.date=t.date+1 where s.erl=0 and (t.erl<>0 or t.date is null)
         union all
         select s.* ,cnt=case when s.erl=0 then cnt+1 else 0 end 
         from tb s join wang t on s.date=t.date+1 )
select distinct s.* from wang s,wang t where s.date=t.date+1 and t.cnt=5 and s.cnt=0 2009-10-22 00:00:00.000 1 720 67276 0

Answer 5

if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([Date] datetime,[BssID] int,[BtsID] int,[ERL] int)
insert [tb]
select '2009-10-14 00:00:00',1,720,0 union all
select '2009-10-15 00:00:00',1,720,2729 union all
select '2009-10-16 00:00:00',1,720,44807 union all
select '2009-10-17 00:00:00',1,720,0 union all
select '2009-10-18 00:00:00',1,720,0 union all
select '2009-10-19 00:00:00',1,720,0 union all
select '2009-10-20 00:00:00',1,720,0 union all
select '2009-10-21 00:00:00',1,720,0 union all
select '2009-10-22 00:00:00',1,720,67276
go
create function f_ck(@dt Datetime)
returns int
as
begin
declare @result int
declare @i int
set @result = 1
set @i = -1
while @i > -6
begin
if not exists(select 1 from tb where [Date] = dateadd(dd , @i,@dt) and [ERL] = 0)
begin
set @result = 0
break
end
set @i = @i - 1
end
return @result
endselect * from tb where dbo.f_ck([Date]) = 1
-----------------------------------------
Date                    BssID       BtsID       ERL
----------------------- ----------- ----------- -----------
2009-10-22 00:00:00.000 1           720         67276(1 行受影响)

Answer 6

create table test(Data datetime ,BssID  int ,BtsID int, ERL int )insert  test 
select 
'2009-10-14 00:00:00', '1' ,720, 0
union all  select
'2009-10-15 00:00:00', 1, 720, 2729 
union all select
'2009-10-16 00:00:00', 1, 720, 44807
union all select
'2009-10-17 00:00:00', 1, 720, 0 
union all select
'2009-10-18 00:00:00', 1, 720, 0 
union all select
'2009-10-19 00:00:00', 1, 720, 0 
union all select
'2009-10-20 00:00:00', 1, 720, 0 
union all select
'2009-10-21 00:00:00', 1, 720, 0 
union all select
'2009-10-22 00:00:00', 1, 720, 67276 create proc Find_Rows
as
begin
declare @table table (id int identity(1,1), Data datetime ,BssID int,BtsID int , ERL int  )
insert into @table select * from test
declare @maxID int, @i int,@ERL1 int, @ERL2 int,@ERL3 int,@ERL4 int ,@ERL5 int
select @maxID =  max(id) from @table 
set @i = 1
while @i<= @maxID-5
begin
select   @ERL1 = ERL from @table where id  = @i
          if (@ERL1=0)
           begin 
            select   @ERL2 = ERL from @table where id  = @i+1
    if (@ERL2=0)
     begin
              select   @ERL3 = ERL from @table where id  = @i+2
        if (@ERL3=0)
          begin
                   select   @ERL4 = ERL from @table where id  = @i+3
    if (@ERL4=0)
            begin
                      select   @ERL5 = ERL from @table where id  = @i+4
      if (@ERL5=0)
      begin
       select * from @table where id  = @i+4
                      end
    end
   end
         end
end    set @i= @i+1
  end
end
execute Find_Rows--result
/*
id          Data                    BssID       BtsID       ERL
----------- ----------------------- ----------- ----------- -----------
8           2009-10-21 00:00:00.000 1           720         0(1 row(s) affected)
*/