查询各列，连续为0的的最大次数

[czkun]

查询各列，连续为0的的最大次数

结果：3 2 3 3 1 3

这语句，怎么写

[sdhp]

结果明显是323323

[czkun]

楼上对的，看错了，这语句该如何写呢，头疼，不想用游标去循环

[哥眼神纯洁不]

with tb(a,num1,num2,num3)as(
select 1,1,0,1 union
select 2,0,2,3 union
select 3,1,0,0 union
select 4,2,0,1 union
select 5,0,1,0 union
select 6,0,1,1 union
select 7,0,1,0 union
select 8,1,0,0 union
select 9,1,0,0 union
select 10,1,0,2
),tc as(
select a,
case when num1=0 then 1 else 0 end num1,
case when num2=0 then 1 else 0 end num2,
case when num3=0 then 1 else 0 end num3
 from tb where a=1
union all
select tb.a,
case when tb.num1=0 then tc.num1+1 else 0 end,
case when tb.num2=0 then tc.num2+1 else 0 end,
case when tb.num3=0 then tc.num3+1 else 0 end
from tc,tb where tc.a=tb.a-1)
select MAX(num1),MAX(num2),MAX(num3) from tc

可以用递归写~

[chen357313771]

IF OBJECT_ID('tempdb..#tem','U') IS NOT NULL DROP TABLE #tem
CREATE TABLE #tem
(
	ID INT IDENTITY(1,1)
	,num1 INT
	,num2 INT
	,num3 INT  
)
INSERT INTO #tem
VALUES (0,1,1),(1,0,0),(2,0,0),(3,1,0),(0,2,1),(0,3,2),(1,0,3),(0,1,4),(0,2,5),(0,3,0)
		,(1,0,0),(0,1,1),(0,2,2),(1,3,0),(0,0,1),(1,0,2),(2,1,0),(0,0,1),(0,1,2)
;WITH test AS
(
	SELECT ROW_NUMBER() OVER(PARTITION BY num1 ORDER BY ID) num1ID
		,ROW_NUMBER() OVER(PARTITION BY num2 ORDER BY ID) num2ID
		,ROW_NUMBER() OVER(PARTITION BY num3 ORDER BY ID) num3ID
		,ROW_NUMBER() OVER(ORDER BY ID) n
		,*
	FROM #tem
)
SELECT (SELECT MAX(counts1) FROM (
			SELECT ROW_NUMBER() OVER(PARTITION BY  n - num1ID ORDER BY ID) counts1
			FROM test
		WHERE num1=0  ) AS A ) AS MaxNum1
	,(SELECT MAX(counts2) FROM (
		SELECT ROW_NUMBER() OVER(PARTITION BY  n - num2ID ORDER BY ID) counts2
		FROM test
		WHERE num2=0  ) AS B )  AS MaxNum2
	,(SELECT MAX(counts3) FROM (
		SELECT ROW_NUMBER() OVER(PARTITION BY  n - num3ID ORDER BY ID) counts3
		FROM test
		WHERE num3=0  ) AS B ) AS MaxNum2

/*
MaxNum1	MaxNum2	MaxNum2
3		2		3

*/