请高手帮忙写下sql语句

Question

有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" "李四" "王五,钱六" 我想了好久，不知道怎么写，请高手帮忙 

Answer 1

其实，我在ms sqlserver中已经解决问题了，后来才知道，对方是oracle数据库，郁闷的要死，先将sqlserver中的代码贴出来，希望能有点用
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)>0return(stuff(@re, 1, 1, ''))
endselect 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)

Answer 2

感觉是表的结构设计有问题,应该建立纵向表来进行关联查询,而不是横向的查询，
如果要查询也只能是建函数来处理了,比如说建一个changetoname函数用来转换
这个函数的功能用来关联user表并且解析id,将id转换成name后再拼串，函数写完后
最后的查询应该如下
select meetingid,changetoname(createrid),changetoname(joinid)
from meetinginfo

Answer 3

SELECT  a.meetingid,a.meetingname,DECODE(b.userid,'vk001',b.username,'') as name  FROM meetinginfo a,userinfo b

Answer 4

select a.meetingid, meetingname, b.username, c.usernames
from meetinginfo a , userinfo b, ( select c1.userid||','||c2.userid userids, c1.username||','||c2.username usernames from userinfo c1, userinfo c2) c
where a.createrid = b.userid
and a.joinid = c.userids

Answer 5

临时写的:
SQL> select * from meetinginfo;MEETINGID            MEETINGNAM CREATERID  JOINID
-------------------- ---------- ---------- ------------------------------
m1                   PM         vk001      vk002,vk003
m2                   GM         CE001      vk001,tk001SQL> select meetingid,meetingname,f_h(joinid) aa from meetinginfo;MEETINGID            MEETINGNAM AA
-------------------- ---------- ------------------------------
m1                   PM         王五,钱六
m2                   GM         李四,许七create or replace function f_h(arg_res in varchar2) return varchar2
is
namelist varchar2(1100);
sss varchar2(30);
i number;
j number;
aa varchar2(1000);
begin
i:=0;
j:=1;
aa:=arg_res||',';       loop
         j:= instr(aa,',',i+1);
         EXIT WHEN j=0;
        sss := substr(aa,i+1,j-i-1);
        select username into sss from userinfo where userid=sss; 
        namelist :=namelist||','||sss;
        i:=j;
     end loop;
     namelist:=substr(namelist,-(length(namelist)-1));
  return(namelist);
end f_h;

Answer 6

String sql = "select * from meetinginfo ";
ResultSet rs=st.executeQuery(sql);
ArrayList al = new ArrayList();
   while (rs.next())
   {
    MeetinginfoBean mbean = new MeetinginfoBean();
    String joinid =rs.getString("joinid ");
    mbean.set(rs.getString("joinid ");
    .............
    al.add(mbean); 
   )
rs.close();
for(int i=0;i<al.size();i++){
  MeetinginfoBean mbean = (MeetinginfoBean)al.get(i);
  String asplit[] = mbean.get(joinid);
  String temp = "";
  for(int j=0;j<asplit.size();j++){
    String sql = "select * from userinfo where userid = asplit[j];
    ResultSet rs=st.executeQuery(sql);
    while (rs.next())
    {
    String joinid =rs.getString("username");
    temp = temp+username+",";//最后要去掉一个,号 
    .............
    )
    rs.close();
    mbean.setTemp(temp);
  }
}现在mbean里的值就是楼主需要得了，你可以任意使用了。

Answer 7

mbean.setTemp(temp);
这行代码应该写在下面的括号外面

Answer 8

就是jdsnhan(柳荫凉) 这样了,但是你这个设计好像和数据库设计的低范式都没有达到,这样的设计带来的这样统计麻烦可想而知.要是数据量大的话就完了