sql经典题1

有一个这样的字符串 1Q,1T,1Q,1m,1m,1m,1Q,1P,2T,1R
怎样显示结果为3Q+3T+3m+1P+1R

create function dbo.fn_split 
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as

begin
declare @i int

set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)


while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))

set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end

if @inputstr <> '\'
insert @temp values(@inputstr)

return
end
go

--调用

declare @s varchar(1000)

set @s='1Q,1T,1Q,1m,1m,1m,1Q,1P,2T,1R'

select sum(cast(left(a,1) as int)) c1 , right(a,1) c2 into tmp from dbo.fn_split(@s,',') group by right(a,1)

go

declare @sql varchar(1000)
set @sql = ''
select @sql = @sql + cast(t.c1 as varchar) + cast(t.c2 as varchar) + '+' from (select c1,c2 from tmp) as t
set @sql='select result = ''' + left(@sql , len(@sql) - 1) + ''''
exec(@sql)

drop function dbo.fn_split
drop table tmp

/*
result
--------------
3m+1P+3Q+1R+3T
*/
posted @ 2011-09-15 15:54  佳园  阅读(1064)  评论(0编辑  收藏  举报