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
*/