round result
1 -1
2 -1
3 -1
4 1
5 -1
6 -1
7 1
8 1
两列
重新组合成这样
result count
-1 3
1 1
-1 2
1 2
可以完成吗
------解决方案--------------------
select 1 [round],-1 result
into #t
union all select 2,-1
union all select 3,-1
union all select 4,1
union all select 5,-1
union all select 6,-1
union all select 7,1
union all select 8,1
select result,count(1) [count]
from
(
select *,ROW_NUMBER() over(partition by result order by round) rn
from #t
) t
group by [round]-rn,result;
------解决方案--------------------
create table kt
(round int, result int)
insert into kt
select 1, -1 union all
select 2, -1 union all
select 3, -1 union all
select 4, 1 union all
select 5, -1 union all
select 6, -1 union all
select 7, 1 union all
select 8, 1
select a.result,
(select count(1) from kt c
where c.round<=a.round and c.result=a.result
and not exists(select 1 from kt d
where d.round between c.round and a.round
and d.result<>c.result)) 'count'