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

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

问题如下(以Oracle数据库为例)：

select * from T1;

ID NAME AGE MOBILE

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

-----------

1 yanghengli18 13830557XXX

2 lijiajie18 13325116XXX

select * from T2;

ID BOOKNAMEOWNER_IDBOOKDESC

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

1 test11test first

2 test11test first

3 test12test lijiajie 1

4 test12test lijiajie 2

需要得到如下结果：

1 yanghengli 13830557XXXtest first,test first

2 lijiajie13325116XXXtest 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_NAMET1_MOBILET2_BOOKDESC

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

1 yanghengli13830557XXX,test first,test first

2lijiajie13325116XXX,test lijiajie 1,test lijiajie 2

说明：

这个问题基本上可以通过以下方法来应对：

1、通过在数据库内自定义函数来处理

2、通过自定义存储过程来处理

3、编程逐行遍历数据，自行组合字符串

4、通过SQL+数据库提供的文件输出功能。

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