Sql 分组求和语句。

if object_id(‘tb’) is not null drop table tb

go

create table tb(编号 varchar(10),颜色 nvarchar(10),形状 nvarchar(10))

insert into tb values(’001′ ,’红色’ ,’圆形’)

insert into tb values(’005′ ,’红色’ ,’圆形’)

insert into tb values(’006′ ,’红色’ ,’圆形’)

insert into tb values(’008′ ,’红色’ ,’圆形’)

insert into tb values(’011′ ,’黑色’ ,’球形’)

insert into tb values(’015′ ,’黄色’ ,’方形’)

insert into tb values(’026′ ,’红色’ ,’圆形’)

insert into tb values(’031′ ,’红色’ ,’圆形’)

insert into tb values(’035′ ,’黄色’ ,’球形’)

insert into tb values(’036′ ,’黑色’ ,’球形’)

insert into tb values(’039′ ,’黑色’ ,’球形’)

insert into tb values(’055′ ,’黑色’ ,’球形’)

go

 

select fid=0,id=0,颜色+形状 as 颜色形状 into # from tb order by 编号

select * from #

declare @颜色形状 nvarchar(20),@i int,@j int

update # set id=@i,fid=@j,

@i=case when @颜色形状=颜色形状 then isnull(@i,0)+1 else 1 end,

@j=case when @颜色形状=颜色形状 then isnull(@j,0) else isnull(@j,0)+1 end,

@颜色形状=颜色形状

select * from #

 

select ltrim(max(id))+颜色形状 as 结果 from # group by fid,颜色形状 order by fid

 

 

drop table #

/* 结果

——————————–

4红色圆形

1黑色球形

1黄色方形

2红色圆形

1黄色球形

3黑色球形

(6 行受影响) */