求一sql语句

[cxudong]

id 颜色
1 红
2 红
3 黄
4 红
5 黄
6 红
7 兰
8 兰

转换成

颜色 数量 间隔
红 4 3
黄 2 2
兰 2 1

主要是同种颜色不连续出现的次数

[duanzhi1984]

间隔 怎么计算的。兰为何为1

[duanzhi1984]

create table #tb(id int,颜色 varchar(10))
insert into #tb

select 1, '红'union all
select 2 ,'红'union all
select 3, '黄'union all
select 4, '红'union all
select 5 ,'黄'union all
select 6 ,'红'union all
select 7 ,'兰' union all
select 8 ,'兰'






select 颜色,count(颜色) 颜色,sum(间隔)间隔 from

(select a.颜色,isnull((select count(1) from #tb b where b.颜色<>a.颜色 and a.id+1=b.id group by b.颜色),0) 间隔 from #tb a )  as c 

group by 颜色



颜色         颜色          间隔
---------- ----------- -----------
红          4           3
黄          2           2
兰          2           0

(3 行受影响)

[cxudong]

兰色没被隔开就是1

[duanzhi1984]

create table #tb(id int,颜色 varchar(10))
insert into #tb

select 1, '红'union all
select 2 ,'红'union all
select 3, '黄'union all
select 4, '红'union all
select 5 ,'黄'union all
select 6 ,'红'union all
select 7 ,'兰' union all
select 8 ,'兰'






select 颜色,count(颜色) 颜色,case when sum(间隔)=0 then 1 else sum(间隔) end 间隔 from

(select a.颜色,isnull((select count(1) from #tb b where b.颜色<>a.颜色 and a.id+1=b.id group by b.颜色),0) 间隔 from #tb a )  as c 

group by 颜色


颜色         颜色          间隔
---------- ----------- -----------
红          4           3
黄          2           2
兰          2           1

(3 行受影响)

[cxudong]

很好，学习了