欢迎来到Doc100.Net免费学习资源知识分享平台!
您的位置:首页 > 程序异常 >

请高手帮忙写下sql语句,该怎么处理

更新时间: 2014-05-15 09:27:45 责任编辑: Author_N4

 

请高手帮忙写下sql语句
有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 "   "李四 "   "王五,钱六 "  

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


--参考方法--
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
--参考方法--
临时写的:
SQL> select * from meetinginfo;

MEETINGID MEETINGNAM CREATERID JOINID
-------------------- ---------- ---------- ------------------------------
m1 PM vk001 vk002,vk003
m2 GM CE001 vk001,tk001

SQL> 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;

--参考方法--

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);
上一篇:上一篇
下一篇:下一篇

 

随机推荐程序问答结果

 

 

如对文章有任何疑问请提交到问题反馈,或者您对内容不满意,请您反馈给我们DOC100.NET论坛发贴求解。
DOC100.NET资源网,机器学习分类整理更新日期::2014-05-15 09:27:45
如需转载,请注明文章出处和来源网址:http://www.doc100.net/bugs/t/446655/
本文WWW.DOC100.NET DOC100.NET版权所有。