数据库为oracle
有二个表 st1 st2结构如下
st1表结构
stid member primary
stname varchar2
st2表结构
id member primary
stid member (st1表中的主键)
name varchar2
二个表为1对多的关系
我想根据二个的关联查询出st1表的stid,stname和st2表的name
问题在把st2表的name和在一起,形成一条记录,不是分为多条记录
比如数据
st1表
stid stname
1 stname1
st2表
id stid name
1 1 name1
2 1 name2
输出的为
1 stname1 name1,name2 (把st2的相对应的name加在一起以,分开)
有二个表 st1 st2结构如下
st1表结构
stid member primary
stname varchar2
st2表结构
id member primary
stid member (st1表中的主键)
name varchar2
二个表为1对多的关系
我想根据二个的关联查询出st1表的stid,stname和st2表的name
问题在把st2表的name和在一起,形成一条记录,不是分为多条记录
比如数据
st1表
stid stname
1 stname1
st2表
id stid name
1 1 name1
2 1 name2
输出的为
1 stname1 name1,name2 (把st2的相对应的name加在一起以,分开)
标题:合并相同列的数据
作者:爱新觉罗.毓华
时间:2008-05-05
地点:广东深圳
*//*情况说明
NAME USERID
张三 KB001
张三 KB003
李四 KB001
李四 KB002
李四 KB003用SQL实现表示如下:
NAME USERID
张三 KB001,KB003
李四 KB001,KB002,KB003
*/create table tb(NAME varchar2(10) , USERID varchar2(10))
insert into tb values('张三' , 'KB001');
insert into tb values('张三' , 'KB003');
insert into tb values('李四' , 'KB001');
insert into tb values('李四' , 'KB002');
insert into tb values('李四' , 'KB003');--1
select name,rtrim(
max(decode(USERID , 'KB001' , USERID || ',' , '')) ||
max(decode(USERID , 'KB002' , USERID || ',' , '')) ||
max(decode(USERID , 'KB003' , USERID || ',' , '')),',') userid
from tb
group by name--2
SELECT NAME, ltrim(MAX(sys_connect_by_path(userid , ',')) , ',') userid
FROM (SELECT NAME , userid , row_number() over(PARTITION BY NAME ORDER BY userid) rn , rownum prn FROM tb)
START WITH rn = 1
CONNECT BY prn - 1 = PRIOR prn AND NAME = PRIOR NAME
GROUP BY NAME
ORDER BY NAME;drop table tb/*
NAME USERID
---------- ---------------------------------
李四 KB001,KB002,KB003
张三 KB001,KB003
2 rows selected
*/
insert into t2 values('001','vl1');
insert into t2 values('001','vl2');
insert into t2 values('001','vl3');
insert into t2 values('002','vl1');
insert into t2 values('002','vl2'); SELECT COL1, LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2, ',')), ',') COL2
FROM (SELECT COL1,
COL2,
MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN,
(ROW_NUMBER() OVER(ORDER BY COL1, COL2)) +
(DENSE_RANK() OVER(ORDER BY COL1)) NUMID
FROM T2)
START WITH COL2 = COL2_MIN
CONNECT BY NUMID - 1 = PRIOR NUMID
GROUP BY COL1 /*
COL1 COL2
---------- ----------------------------------------
001 vl1,vl2,vl3
002 vl1,vl2
2 rows selected
*/