如何通过SQL将不同数据库表中记录两行合并为一行

如何通过SQL将不同数据库表中记录两行合并为一行

问题如下（以Oracle数据库为例）：

select * from T1;

ID NAME         AGE    MOBILE
---------- ---------- ---------- -----------
1 yanghengli    18     13830557XXX
2 lijiajie      18     13325116XXX

select * from T2;

ID BOOKNAME             OWNER_ID  BOOKDESC
---------------------------------------------------------------
1 test1                 1         test first
2 test1                 1         test first
3 test1                 2         test lijiajie 1
4 test1                 2         test lijiajie 2

 

需要得到如下结果： 

1     yanghengli 13830557XXX   test first,test first
2     lijiajie   13325116XXX   test lijiajie 1,test lijiajie 2 

尝试方法：

create table t3(
t1_id int,
t1_name varchar2(10),
t1_mobile varchar2(11),
t2_bookdesc varchar2(100)
);

 
insert into t3(t1_id,t1_name,t1_mobile) (select id,name,mobile from t1); 

spool c:\\test.txt;
select 'update t3 set t2_bookdesc = concat(t2_bookdesc,''' || concat(',',bookdesc) || ''') where t1_id = ' || owner_id || ';' from t2;
spool off;

打开c:\\test.txt，得到如下内容：

SQL> select 'update t3 set t2_bookdesc = concat(t2_bookdesc,''' || concat(',',bookdesc) || ''') where t1_id = ' || owner_id || ';' from t2;

 

'UPDATET3SETT2_BOOKDESC=CONCAT(T2_BOOKDESC,'''||CONCAT(',',BOOKDESC)||''')WHERET

--------------------------------------------------------------------------------

update t3 set t2_bookdesc = concat(t2_bookdesc,',test first') where t1_id = 1; 

update t3 set t2_bookdesc = concat(t2_bookdesc,',test first') where t1_id = 1; 

update t3 set t2_bookdesc = concat(t2_bookdesc,',test lijiajie 1') where t1_id =

 2;                                                                            

                                                                               

update t3 set t2_bookdesc = concat(t2_bookdesc,',test lijiajie 2') where t1_id =

 2;                                                                            

                                                                               

 

SQL> spool off;

 

对该文件进行编辑，删除select结果的头尾，得到如下内容：

 

update t3 set t2_bookdesc = concat(t2_bookdesc,',test first') where t1_id = 1; 
update t3 set t2_bookdesc = concat(t2_bookdesc,',test first') where t1_id = 1; 
update t3 set t2_bookdesc = concat(t2_bookdesc,',test lijiajie 1') where t1_id =

 2;          

 update t3 set t2_bookdesc = concat(t2_bookdesc,',test lijiajie 2') where t1_id =

 2;                                                                             

 

在sql窗口执行以下语句：
@c:\\test.txt

 

执行完毕之后，再检查t3表中内容：

select * from t3;

T1_ID T1_NAME       T1_MOBILE     T2_BOOKDESC
--------------------------------------------------------------------------------------
1     yanghengli    13830557XXX    ,test first,test first
2     lijiajie      13325116XXX    ,test lijiajie 1,test lijiajie 2

说明：
这个问题基本上可以通过以下方法来应对：
1、通过在数据库内自定义函数来处理
2、通过自定义存储过程来处理
3、编程逐行遍历数据，自行组合字符串
4、通过SQL+数据库提供的文件输出功能。

第1、2种方法主要是通过数据库游标来遍历组合字符串，第3种方法就不再赘述，本文即是利用第4种方法。