select id,name,descs from table1 t1,table2 t2 where t1.type=t2.type;
不用 group by ???不对的,不能实现的。
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
....那就给你另外一种解决方案...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
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;
where t1.type=t2.type;
不用 group by ???不对的,不能实现的。
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
....那就给你另外一种解决方案...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
多谢谢,好汉指教。我想请问一下,你的语句,这般长,我用java如何调用,第一次见这般长代码。请指教。
from table2 a, table1 b
where a.type(+)=b.type
group by b.name;