最大连续出现的次数

最新推荐文章于 2023-12-13 07:47:47 发布

Felixzhaowenzhong

最新推荐文章于 2023-12-13 07:47:47 发布

阅读量937

点赞数

分类专栏： SQL SERVER2008 文章标签： table rest c

本文链接：https://blog.csdn.net/zhaowenzhong/article/details/7549284

版权

SQL SERVER2008 专栏收录该内容

65 篇文章 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