二表相连生成新数据，用group by未成功，有难度。

[bonlog]

二表通过type相连，
在mysql下，很易实现，但oracle中，不会。
谢谢大家，祝好人二生平安。

[lhdz_bj]

哦，抱歉，没仔细看结果，还要把descs拼接起来。

[bonlog]

引用 7 楼 ziwen00 的回复:

.……

多谢谢，好汉指教。 我想请问一下，你的语句，这般长，我用java如何调用，第一次见这般长代码。请指教。

[ruihuahan]

11gR2 新增了一个很有意思的函数 listagg ，可以按组连接字符串。 select b.name, listagg(a.desc, ', ') within group (order by a.desc) from table2 a, table1 b where a.type(+)=b.type group by b.name;

[lhdz_bj]

select id,name,descs from table1 t1,table2 t2 where t1.type=t2.type;

[bonlog]

引用 1 楼 LHDZ_BJ 的回复:

select id,name,descs from table1 t1,table2 t2 where t1.type=t2.type;

不用 group by ？？？ 不对的，不能实现的。

[lhdz_bj]

不就是连接两个表吗？ 说清楚你的需求。

[睿智天成]

select a.type id,a.name type,wmsys.wm_concat（b.desc）descs from table1 a left join table2 b on a.type=b.type group by id

[bonlog]

引用 4 楼 Rexmax 的回复:

select a.type id,a.name type,wmsys.wm_concat（b.desc）descs from table1 a left join table2 b on a.type=b.type group by id

多谢谢，此函数，我学习了。 祝天天 快乐！！

[陈字文]

上面已经已经有答案了..

WITH A AS(
  SELECT 1 AS TYPE,'NAME1' AS NAME,1 AS ID,'WC1' AS DES FROM DUAL UNION ALL
  SELECT 2,'NAME2',2,'WC2' FROM DUAL UNION ALL
  SELECT 3,'NAME3',3,'WC3' FROM DUAL UNION ALL
  SELECT 4,'NAME4',4,'WC4' FROM DUAL
),B AS(
  SELECT 1 AS ID,1 AS TYPE,'WC1' AS DES FROM DUAL UNION ALL
  SELECT 2,2,'WC2' FROM DUAL UNION ALL
  SELECT 3,2,'WC3' FROM DUAL UNION ALL
  SELECT 4,4,'WC4' FROM DUAL
)
SELECT A.ID, A.NAME, WMSYS.WM_CONCAT(B.DES)
  FROM A, B
 WHERE A.TYPE = B.TYPE(+)
 GROUP BY A.ID, A.NAME;

结果 ID NAME WMSYS.WM_CONCAT(B.DES) 1 NAME1 WC1 2 NAME2 WC2,WC3 3 NAME3 4 NAME4 WC4

[陈字文]

引用 5 楼 bonlog 的回复:

引用 4 楼 Rexmax 的回复:select a.type id,a.name type,wmsys.wm_concat（b.desc）descs from table1 a left join table2 b on a.type=b.type group by id 多谢谢，此函数，我学习了。 祝天天 快乐！！

....那就给你另外一种解决方案...

WITH A AS(
  SELECT 1 AS TYPE,'NAME1' AS NAME,1 AS ID,'WC1' AS DES FROM DUAL UNION ALL
  SELECT 2,'NAME2',2,'WC2' FROM DUAL UNION ALL
  SELECT 3,'NAME3',3,'WC3' FROM DUAL UNION ALL
  SELECT 4,'NAME4',4,'WC4' FROM DUAL
),B AS(
  SELECT 1 AS ID,1 AS TYPE,'WC1' AS DES FROM DUAL UNION ALL
  SELECT 2,2,'WC2' FROM DUAL UNION ALL
  SELECT 3,2,'WC3' FROM DUAL UNION ALL
  SELECT 4,4,'WC4' FROM DUAL
)
SELECT ID,NAME,LTRIM(MAX(SYS_CONNECT_BY_PATH(DES, ',')), ',') AS DES FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY A.ID ORDER BY NAME) R,A.ID,A.NAME,B.DES
  FROM A, B
 WHERE A.TYPE = B.TYPE(+)
 )
 START WITH R = 1
 CONNECT BY PRIOR R=R-1
 AND PRIOR ID =ID
 GROUP BY ID,NAME
 ORDER BY 1