求教一个script
round result
1-1
2-1
3-1
41
5-1
6-1
71
81
两列
重新组合成这样
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'
from kt a
left join kt b on a.round=b.round-1
where a.result<>b.result or b.round is null
/*
result count
----------- -----------
-1 3
1 1
-1 2
1 2
(4 row(s) affected)
*/
