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

代码语言

代码分类

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

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


	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)