最大连续出现的次数代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】最大连续出现的次数

作者:zhaowenzhong / 发布于2013/3/26/ 727


	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

试试其它关键字

　连续出现　

同语言下

可能有用的

zhaowenzhong贡献的其它代码(7)