最大连续出现的次数

淩亂°似流年 2022-06-12 08:51 175阅读 0赞

with CET_Table as  
    (
    select 'A' name,1 as [month],0 as value
    union all select 'A',2,1
    union all select 'A',3,0
    union all select 'A',4,0  
    union all select 'A',5,1
    union all select 'A',6,0  
    union all select 'A',7,0  
    union all select 'A',8,0  
    union all select 'A',9,0  
    union all select 'A',10,0  
    union all select 'A',11,1  
    union all select 'A',12,1   
    union all select 'B',1,1
    union all select 'B',2,0
    union all select 'B',3,0
    union all select 'B',4,1
    union all select 'B',5,1
    union all select 'B',6,1
    union all select 'B',7,1
    union all select 'B',8,1
    union all select 'B',9,0
    union all select 'B',10,0
    union all select 'B',11,0
    union all select 'B',12,1
    )
    --select * into ta from CET_Table ---根据CET快速创建一个表
    ,A as(
    select * ,number = (select COUNT(1) from CET_Table B where B.name = A.name and B.value = A.value and B.month<=A.month
    and B.month>=(select isnull(max(c.month),1) from CET_Table C where C.name = A.name and C.value <> A.value and C.month<=A.month))
    from CET_Table A)
    ,B as(
    select name,max(number)'0',0 as'1' from a   where  value=0  group by name
    union all
    select name,0 as '-',max(number) as'-' from a   where  value=1  group by name
    ) 
    select name ,SUM([0])max_rest_time,SUM([1])max_working_time from b  group by name
    
    ---明细
    
    --select * ,number = (select COUNT(1) from CET_Table B where B.name = A.name and B.value = A.value and B.month<=A.month
    --and B.month>=(select isnull(max(c.month),1) from CET_Table C where C.name = A.name and C.value <> A.value and C.month<=A.month))
    --from CET_Table A