日期:2014-05-18  浏览次数:20477 次

在存储过程中如何得到这样的结果?
有表   a:
id                 ids
1                 1,2,3
2                 1,3,4
3                 2,3,5
表   b:
id                 name
1                     a
2                     b
3                     c
4                     d
5                     e

a表中的ids对应b表中的id字段
要得到的结果是:
a_id             names
1                   a,b,c
2                   a,c,d
3                   b,c,e

请各位高手指教,谢谢!

------解决方案--------------------
create table a(id int,ids varchar(10))
insert into a
select 1, '1,2,3 ' union all
select 2, '1,3,4 ' union all
select 3, '2,3,5 '

create table b(id int,name varchar(10))
insert into b
select 1, 'a ' union all
select 2, 'b ' union all
select 3, 'c ' union all
select 4, 'd ' union all
select 5, 'e '

GO
create function dbo.fn_str(@id int)
returns varchar(20)
AS
begin
declare @str varchar(20)
set @str= ' '
select @str=@str+ ', '+b.name from a,b
where charindex( ', '+rtrim(b.id)+ ', ', ', '+a.ids+ ', ')> 0
and a.id=@id
set @str=stuff(@str,1,1, ' ')
return @str
end
GO

--顯示
select a.id as a_id,dbo.fn_str(a.id) as names
from a
group by a.id

/*
a_id names
----------- ----------
1 a,b,c
2 a,c,d
3 b,c,e
*/
--
drop table a,b
drop function fn_str
------解决方案--------------------
写个自定义函数
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

alter FUNCTION f_splitSTR(
@s varchar(8000),
@split varchar(10)
)RETURNS varchar(100)
AS
BEGIN
DECLARE @splitlen int
DECLARE @re varchar(100)
SET @re= ' '
SET @splitlen=LEN(@split+ 'a ')-2
SET @s=@s+@split
WHILE CHARINDEX(@split,@s)> 0
BEGIN
SELECT @re=@re+ ', '+name
from b
where id=LEFT(@s,CHARINDEX(@split,@s)-1)
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen, ' ')
END
RETURN Right(@re,LEN(@re)-1)
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

然后执行:
select id,dbo.f_splitSTR(ids, ', ')
from a

------解决方案--------------------
drop table #cc

select *,ids as newids into #cc from aa
update #cc set newids=(replace(#cc.newids, ' '+convert(varchar(10),bb.id)+ ', ', ' '+bb.name+ ', '))
from #cc,bb where #cc.ids like ' '+convert(varchar(10),bb.id)+ ',% '

update #cc set newids=(replace(#cc.newids, ', '