在存储过程中如何得到这样的结果?
有表 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, ', '+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, ', '+convert(varchar(10),bb.id)+ ' ', ', '+bb.name+ ' '))
from #cc,bb where #cc.ids like '%, '+convert(varchar(10),bb.id)+ ' '
select * from #cc
/*
测试结果
-------
ididsnewids
11,2,3a,b,c
21,3,4a,c,d
32,3,5b,c,e
*/
