我快崩溃了，大家来帮帮忙吧

[lix511]

有这样两张表：
表A（主键AID） 表B（主键BID）
AID Name BID AID Name Text
1 a 1 1 x a1
2 b 2 1 y a2
3 c 3 1 z a3
4 2 r a4
5 3 s a5

现在两表关联查询，希望出来这样的结果：
AID A.Name Text
1 a a1,a2,a3
2 b a4
3 c a5

不能在程序里处理...所以该怎么写这个查询SQL？
可以用临时表...

有这样两张表：
表A（主键AID） 表B（主键BID）
AID Name BID AID Name Text
1 a 1 1 x a1
2 b 2 1 y a2
3 c 3 1 z a3
4 2 r a4
5 3 s a5

现在两表关联查询，希望出来这样的结果：
AID A.Name Text
1 a a1,a2,a3
2 b a4
3 c a5

不能在程序里处理...所以该怎么写这个查询SQL？
可以用临时表...

[bjt_]

树型查询+分析函数

[lix511]

老大，不明白，具体解释一下啊

[淡定的峰哥]

又是一个合并报表的问题
把我的这个代码拿去看一下

/*现在有如下两张表分别为:
表A
ID Name
1 张三
2 李四
表B
ID XMMC CODE RYID
1 A项目一 XMONE 1
2 A项目二 XMTWO 1
3 A项目三 XMTHREE 1
4 B项目一 XMONE 2

要求一条SQL语句实现如下结果

表C
ID NAME XMONE XMTWO XMTHREE
1 张三 A项目一 A项目二 A项目三
2 李四 B项目一 [空] [空]

*/
--建表
create table testa
(
id int,
name varchar2(20)
);
create table testb
(
id int ,
xmmc varchar2(20),
code varchar2(20),
ryid int
);
--原始数据
insert into testa values( 1,'张三' );
insert into testa values( 2,'李四 ' );
insert into testb values( 1,'A项目一','XMONE',1 );
insert into testb values( 2,'A项目二','XMTWO', 1 );
insert into testb values( 3,'A项目三','XMTHREE',1 );
insert into testb values( 4,'B项目一','XMONE',2 );
--创建视图
create or replace view v_one
as
select a.id,a.name,b.xmmc as xmone
from testa a,testb b
where a.id= b.ryid and code='XMONE'

create or replace view v_two
as
select a.id,a.name,b.xmmc as xmtwo
from testa a,testb b
where a.id= b.ryid and code='XMTWO'

create or replace view v_three
as
select a.id,a.name,b.xmmc as xmthree
from testa a,testb b
where a.id= b.ryid and code='XMTHREE'
--生成报表
select a.*,b.xmtwo ,c.xmthree from v_one a,v_two b ,v_three c where a.id=b.id(+) and a.id=c.id(+)


如果要想三列在一个字段里显示用||这个连接符就可以了

[littleboys]

table:
CREATE TABLE TEST (XM VARCHAR(8), SL INTEGER);
INSERT INTO TEST
VALUES ('王一', 2),
('王一', 5),
('张二', 4),
('张二', 5),
('张二', 8),
('李三', 2),
('李三', 4),
('李三', 15),
('李三', 29);


WITH B (FATHER,SON,XM,CHAIN) AS
(SELECT A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON,
A.XM, rtrim(ltrim(CHAR(A.SL)))
FROM TEST A
UNION ALL
SELECT C.FATHER,C.SON,C.XM,rtrim(ltrim(B.CHAIN))||','||rtrim(ltrim(C.SL))
FROM (SELECT A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM)) AS FATHER,
A.XM || CHAR(ROW_NUMBER() OVER(PARTITION BY XM) + 1) AS SON,
A.XM, rtrim(ltrim(CHAR(A.SL))) AS SL FROM TEST A) AS C, B
WHERE B.SON= C.FATHER)

SELECT D.XM,D.CHAIN
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY XM ORDER BY LENGTH(CHAIN) DESC) AS ROW_NUM, B.XM,B.CHAIN FROM B) AS D
WHERE D.ROW_NUM = 1;

result:
XM CHAIN
-------- -----------
李三 2,4,15,29
王一 2,5
张二 4,5,8


我在DB2 测试成功，oracle 也应该都有的，

[ShenLiang2025]

现在给出我的答案：
第一次接触 sys_connect_by_path... 话到正题

--table struct

SQL> desc A;
 名称                                                  是否为空? 类型
 ----------------------------------------------------- -------- ------------------------------------
 AID                                                   NOT NULL NUMBER(3)
 NAME                                                           CHAR(4)                                          

SQL> desc B;
 名称                                                  是否为空? 类型
 ----------------------------------------------------- -------- ------------------------------------
 BID                                                   NOT NULL NUMBER(3)
 AID                                                            NUMBER(3)
 NAME                                                           CHAR(4)
 TEXT                                                           CHAR(4)
  
--Datas

SQL> SELECT * FROM A;

       AID NAME
---------- ----
         1 a
         2 b
         3 c

SQL> SELECT * FROM B;

       BID        AID NAME TEXT
---------- ---------- ---- ----
         1          1 x    a1
         2          1 y    a2
         3          1 z    a3
         4          2 r    a4
         5          3 s    a5

Code for You

 
select  m.aid,m.name,substr((sys_connect_by_path(m.text,',')),2) text
from
(
select A.aid,A.NAME,B.text,row_number() over(partition by A.name order by 1) rn,count(A.name) over(partition by A.name order by 1) num_rank from A left join B on A.AID=B.AID
)m
where m.rn=m.num_rank
start with rn=1
connect by rn-1=prior rn and m.NAME=prior m.Name

--RESULT:
  AID NAME TEXT 
  1   a    a1  ,a2  ,a3   
  2   b    a4   
  3   c    a5  

[littleboys]

参考这个帖子 http://topic.csdn.net/u/20071128/15/9112c597-d28b-4649-954e-031c884745d5.html

[lix511]

多谢各位!