sql 某一列接续出现某个值，求最大连续数
sql 某一列连续出现某个值，求最大连续数
列
aa bb cc dd
1 0 1 8
2 0 7 7
0 1 2 0
1 0 0 0
0 0 1 0
0 2 5 6
1 0 0 0
求每一列 0 连续出现的最大个数
希望的结果
aa bb cc dd
2 2 1 3
------解决方案--------------------
------解决方案--------------------
列
aa bb cc dd
1 0 1 8
2 0 7 7
0 1 2 0
1 0 0 0
0 0 1 0
0 2 5 6
1 0 0 0
求每一列 0 连续出现的最大个数
希望的结果
aa bb cc dd
2 2 1 3
------解决方案--------------------
WITH a1 (aa,bb,cc,dd) AS
(
SELECT 1,0,1,8 UNION ALL
SELECT 2,0,7,7 UNION ALL
SELECT 0,1,2,0 UNION ALL
SELECT 1,0,0,0 UNION ALL
SELECT 0,0,1,0 UNION ALL
SELECT 0,2,5,6 UNION ALL
SELECT 1,0,0,0
)
,a2 AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY @@servername) re
FROM a1
)
,b1 AS
(
SELECT MAX(n) n
FROM
(
SELECT COUNT(*) n
FROM
(
SELECT re-ROW_NUMBER() OVER(PARTITION BY aa ORDER BY re) re
FROM a2
WHERE aa=0
) a
GROUP BY re
) b
)
,b2 AS
(
SELECT MAX(n) n
FROM
(
SELECT COUNT(*) n
FROM
(
SELECT re-ROW_NUMBER() OVER(PARTITION BY bb ORDER BY re) re
FROM a2
WHERE bb=0
) a
GROUP BY re
) b
)
,b3 AS
(
SELECT MAX(n) n
FROM
(
SELECT COUNT(*) n
FROM
(
SELECT re-ROW_NUMBER() OVER(PARTITION BY cc ORDER BY re) re
FROM a2
WHERE cc=0
) a
GROUP BY re
) b
)
,b4 AS
(
SELECT MAX(n) n
FROM
(
SELECT COUNT(*) n
FROM
(
SELECT re-ROW_NUMBER() OVER(PARTITION BY dd ORDER BY re) re
FROM a2
WHERE dd=0
) a
GROUP BY re
) b
)
SELECT b1.n aa,b2.n bb,b3.n cc,b4.n dd
FROM b1,b2,b3,b4
------解决方案--------------------
create table t (
a int ,
b int,
c int,
d int)
insert into t(a,b,c,d) values(1,0,1,8)
insert into t(a,b,c,d) values(2,0,7,7)
insert into t(a,b,c,d) values(0,1,2,0)
insert into t(a,b,c,d) values(1,0,0,0)
insert into t(a,b,c,d) values(0,0,1,0)
insert into t(a,b,c,d) values(0,2,5,6)
insert into t(a,b,c,d) values(1,0,0,0)
declare @a int,
@b int,
@c int,
@d int,
@a0 int,
@b0 int,
@c0 int,
@d0 int,
@aCnt int,
@bCnt int,
@cCnt int,
@dCnt int
set @a0=0
set @b0=0
set @c0=0
set @d0=0
set @aCnt=0
set @bCnt=0
set @cCnt=0
set @dCnt=0
declare c cursor for select * from t
open c
fetch from c into @a, @b, @c, @d
while(@@fetch_status = 0)
begin
--处理a列
if @a=0
begin
set @a0=@a0+1
end
else
begin
set @a0=0
end
if @a0>@aCnt
begin
set @aCnt=@aCnt+1
end
--处理b列
if @b=0
begin
set @b0=@b0+1
end
else
begin
set @b0=0
end
if @b0>@bCnt
begin
set @bCnt=@bCnt+1
end
--处理c列
if @c=0
begin
set @c0=@c0+1
end
else
begin
set @c0=0
end
if @c0>@cCnt
begin
set @cCnt=@cCnt+1
end
--处理d列
if @d=0
begin
set @d0=@d0+1
end
else
begin
set @d0=0
end
if @d0>@dCnt