这个能用sql语句解决吗

[lcdcau]

有2张表，表结构如下
表名meetinginfo
meetingid varchar 50 主键 (会议ID)
meetingname varchar 50 (会议名)
createrid varchar 50 (发起人ID)
joinid varchar 50 (参与者ID)

表名userinfo
userid varchar 50 主键 (用户ID)
username varchar 50 (用户名)

表meetinginfo中数据如下
"m1" "ParmentMeeting" "vk001" "vk002,vk003"
"m2" "GloabMeeting" "CEO001" "vk001,tk001"

表userinfo中数据如下
"CEO001" "张三"
"vk001" "李四"
"vk002" "王五"
"vk003" "钱六"
"tk001" "许七"

现在要实现以下结果：
"m1" "ParmentMeeting" "李四" "王五,钱六"

我想了好久，不知道怎么写，请高手帮忙

[子陌红尘]

写一个自定义函数，在函数内部用replace来替换。

[junjimmy]

通过类似substring("tk001",2)的函数就可以出来了，或则另外做字段区分

[lcdcau]

能给个详细完整的代码看看吗

[marco08]

create table meetinginfo (
meetingid varchar(50),
meetingname varchar(50),
createrid varchar(50),
joinid varchar(50)
)
create table userinfo (
userid varchar(50),
username varchar(50)
)
insert meetinginfo select 'm1', 'ParmentMeeting', 'vk001', 'vk002,vk003'
union all select 'm2', 'GloabMeeting', 'CEO001', 'vk001,tk001'

insert userinfo select 'CEO001','张三'
union all select 'vk001','李四'
union all select 'vk002','王五'
union all select 'vk003','钱六'
union all select 'tk001','许七'

create function fun(@userid varchar(200))
returns varchar(2000)
as
begin
declare @re varchar(2000)
set @re=''
select @re=@re+','+username from userinfo
where charindex(userid, @userid)>0

return(stuff(@re, 1, 1, ''))
end

select meetingid, meetingname,
createrid=dbo.fun(createrid),
joinid=dbo.fun(joinid)
from meetinginfo

--result
meetingid meetingname createrid joinid
-------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
m1 ParmentMeeting 李四 王五,钱六
m2 GloabMeeting 张三 李四,许七

(2 row(s) affected)

[leo_lesley]

create table meetinginfo (
meetingid varchar(50) ,
meetingname varchar(50),
createrid varchar(50),
joinid varchar(50) )

create table userinfo (
userid varchar(50),
username varchar(50))

insert meetinginfo
select 'm1','ParmentMeeting','vk001','vk002,vk003'
union all
select 'm2','GloabMeeting','CEO001','vk001,tk001'

insert userinfo
select 'CEO001','张三'
union all
select 'vk001','李四'
union all
select 'vk002','王五'
union all
select 'vk003','钱六'
union all
select 'tk001','许七'

create function f1(@find_str varchar(8000))
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str = ''
select @str = @str+','+username from userinfo where charindex(userid,'vk002,vk003')>0
select @str = stuff(@str,1,1,'')

return @str
end

select meetingid,meetingname,createrid,dbo.f1(joinid) from meetinginfo

[leo_lesley]

呵呵 画蛇添足了。

[lcdcau]

多谢了，已经给分