;with cte as ( select *,row_number() over(order by getdate()) as seq from #a ) select sno,isnull(max(m-seq), 0) as span from( select *,(select top 1 seq from cte where sno=t.sno and seq>t.seq) as m from cte t ) t2 group by sno
;with cte as ( select *,row_number() over(order by getdate()) as seq from #a ) select sno,isnull(max(m-seq), 0) as span from( select *,(select top 1 seq from cte where sno=t.sno and seq>t.seq order by seq) as m from cte t ) t2 group by sno
用这样的,只能单个返回,没做到分组返回。 SELECT p.sno ,MAX(Q.ROW-P.row) MaxLength FROM ( SELECT *,row_number () OVER ( order by getdate()) newRow FROM ( SELECT sno ,row_number () OVER ( order by getdate()) row FROM #a a)m WHERE sno =2 ----传入sno )P,( SELECT *,row_number () OVER ( order by getdate()) newRow FROM ( SELECT sno ,row_number () OVER ( order by getdate()) row FROM #a a)m WHERE sno =2 ----传入sno )Q WHERE P.newRow +1=Q.newRow GROUP BY P.snosno MaxLength ----------- -------------------- 2 12(1 row(s) affected)
2#,3#返回的结果有问题?还是数据问题?偶数行都是不对的哦sno span ----------- -------------------- 1 13 2 5 3 4 4 7 5 0 6 0 7 6 8 3 9 0 Warning: Null value is eliminated by an aggregate or other SET operation.(9 row(s) affected)
DECLARE @intValue INT SET @intValue=6 ----传入值入口,传多个值,数据就会不正确。 SELECT p.sno ,MAX(Q.ROW-P.row) MaxLength FROM ( SELECT *,row_number () OVER ( order by getdate()) newRow FROM ( SELECT sno ,row_number () OVER ( order by getdate()) row FROM #a a)m WHERE sno =@intValue )P,( SELECT *,row_number () OVER ( order by getdate()) newRow FROM ( SELECT sno ,row_number () OVER ( order by getdate()) row FROM #a a)m WHERE sno =@intValue )Q WHERE P.newRow +1=Q.newRow GROUP BY P.sno
;with cte as ( select ROW_NUMBER()over(order by (select 1))as row,* from dbo.test ) select sno,MAX(row1-row)as maxrow from (select sno, row,coalesce((select MIN(row) from cte as c2 where c2.row>c1.row and c1.sno=c2.sno),row)as row1 from cte as c1)as c3 group by sno;
(
select *,row_number() over(order by getdate()) as seq from #a
)
select sno,isnull(max(m-seq), 0) as span
from(
select *,(select top 1 seq from cte where sno=t.sno and seq>t.seq) as m
from cte t
) t2
group by sno
(
select *,row_number() over(order by getdate()) as seq from #a
)
select sno,isnull(max(m-seq), 0) as span
from(
select *,(select top 1 seq from cte where sno=t.sno and seq>t.seq order by seq) as m
from cte t
) t2
group by sno
SELECT p.sno ,MAX(Q.ROW-P.row) MaxLength FROM
(
SELECT *,row_number () OVER ( order by getdate()) newRow FROM (
SELECT sno ,row_number () OVER ( order by getdate()) row
FROM #a a)m
WHERE sno =2 ----传入sno
)P,(
SELECT *,row_number () OVER ( order by getdate()) newRow FROM (
SELECT sno ,row_number () OVER ( order by getdate()) row
FROM #a a)m
WHERE sno =2 ----传入sno
)Q
WHERE P.newRow +1=Q.newRow
GROUP BY P.snosno MaxLength
----------- --------------------
2 12(1 row(s) affected)
----------- --------------------
1 13
2 5
3 4
4 7
5 0
6 0
7 6
8 3
9 0
Warning: Null value is eliminated by an aggregate or other SET operation.(9 row(s) affected)
SET @intValue=6 ----传入值入口,传多个值,数据就会不正确。
SELECT p.sno ,MAX(Q.ROW-P.row) MaxLength FROM
(
SELECT *,row_number () OVER ( order by getdate()) newRow FROM (
SELECT sno ,row_number () OVER ( order by getdate()) row
FROM #a a)m
WHERE sno =@intValue
)P,(
SELECT *,row_number () OVER ( order by getdate()) newRow FROM (
SELECT sno ,row_number () OVER ( order by getdate()) row
FROM #a a)m
WHERE sno =@intValue
)Q
WHERE P.newRow +1=Q.newRow
GROUP BY P.sno
(
select ROW_NUMBER()over(order by (select 1))as row,*
from dbo.test
)
select sno,MAX(row1-row)as maxrow
from (select sno, row,coalesce((select MIN(row)
from cte as c2
where c2.row>c1.row
and c1.sno=c2.sno),row)as row1
from cte as c1)as c3
group by sno;