select replace(WMSYS.WM_CONCAT(id),',',' ') id,name from a group by name 这是10G的用法,10G以下的不能用
字段的连接。 9查找 SYS_CONNECT_BY_PATH 参考例子 Oracle9i中使用SYS_CONNECT_BY_PATH进行行列转换- - 有表: SQL> SELECT deptno, ename FROM emp ORDER BY deptno, ename;DEPTNO ENAME ------ ---------- 10 CLARK 10 KING 10 MILLER 20 ADAMS 20 FORD 20 JONES 20 SCOTT 20 SMITH 30 ALLEN 30 BLAKE 30 JAMES 30 MARTIN 30 TURNER 30 WARD14 rows selected. 想输出为: DEPTNO ENAME ------ ---------- 10 CLARK, KING, MILLER 20 ADAMS, FORD, JONES, SCOTT, SMITH 30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD 除了使用聚集函数或者存储过程之外,9i中可以: SQL> SELECT deptno 2 , LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,',')) 3 KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated 4 FROM ( SELECT deptno 5 , ename 6 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr 7 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev 8 FROM emp ) 9 GROUP BY deptno 10 CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno 11 START WITH curr = 1; DEPTNO----------CONCATENATED---------------------------------------------------------------------------------------------------- 10CLARK,KING,MILLER 20ADAMS,FORD,JONES,SCOTT,SMITH 30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD10CLARK,KING,MILLER 20ADAMS,FORD,JONES,SCOTT,SMITH 30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
select substr(max(sys_connect_by_path(id,' ')),2) id,name from (select a.*,row_number()over(partition by name order by id) rn from a ) group by name start with rn=1 connect by rn-1=prior rn and name=prior name 10G以下可以用这方法,不过语句没做过测试,是贴的另一贴子一位朋友写,稍微修改了下,所以可能有地方没改到的
select t2.kdm_mdid_pk,t2.kdm_title, strcat(t3.subject_mc_content) message from t_knodoc_rel_subjects t1, T_KNO_DOC_METADATA t2, T_SUBJECT_MULTILINGUAL_CONTENT t3 where t1.krs_kdm_mdid_fk=t2.kdm_mdid_pk and t1.krs_subid_fk=t3.subject_mc_id_pk group by t2.kdm_mdid_pk,t2.kdm_title
select t2.kdm_mdid_pk,t2.kdm_title,WMSYS.WM_CONCAT(t3.subject_mc_content) message from t_knodoc_rel_subjects t1, T_KNO_DOC_METADATA t2, T_SUBJECT_MULTILINGUAL_CONTENT t3 where t1.krs_kdm_mdid_fk=t2.kdm_mdid_pk and t1.krs_subid_fk=t3.subject_mc_id_pk group by t2.kdm_mdid_pk,t2.kdm_title
from a
group by name
这是10G的用法,10G以下的不能用
9查找 SYS_CONNECT_BY_PATH 参考例子
Oracle9i中使用SYS_CONNECT_BY_PATH进行行列转换- -
有表:
SQL> SELECT deptno, ename FROM emp ORDER BY deptno, ename;DEPTNO ENAME
------ ----------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD14 rows selected.
想输出为:
DEPTNO ENAME
------ ----------
10 CLARK, KING, MILLER
20 ADAMS, FORD, JONES, SCOTT, SMITH
30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD 除了使用聚集函数或者存储过程之外,9i中可以:
SQL> SELECT deptno
2 , LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
3 KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated
4 FROM ( SELECT deptno
5 , ename
6 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr
7 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
8 FROM emp )
9 GROUP BY deptno
10 CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
11 START WITH curr = 1;
DEPTNO----------CONCATENATED----------------------------------------------------------------------------------------------------
10CLARK,KING,MILLER
20ADAMS,FORD,JONES,SCOTT,SMITH
30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD10CLARK,KING,MILLER 20ADAMS,FORD,JONES,SCOTT,SMITH 30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
from (select a.*,row_number()over(partition by name order by id) rn from a )
group by name
start with rn=1
connect by rn-1=prior rn and name=prior name
10G以下可以用这方法,不过语句没做过测试,是贴的另一贴子一位朋友写,稍微修改了下,所以可能有地方没改到的
select t2.kdm_mdid_pk,t2.kdm_title,
strcat(t3.subject_mc_content) message
from t_knodoc_rel_subjects t1,
T_KNO_DOC_METADATA t2,
T_SUBJECT_MULTILINGUAL_CONTENT t3
where t1.krs_kdm_mdid_fk=t2.kdm_mdid_pk
and t1.krs_subid_fk=t3.subject_mc_id_pk
group by t2.kdm_mdid_pk,t2.kdm_title
from t_knodoc_rel_subjects t1,
T_KNO_DOC_METADATA t2,
T_SUBJECT_MULTILINGUAL_CONTENT t3
where t1.krs_kdm_mdid_fk=t2.kdm_mdid_pk
and t1.krs_subid_fk=t3.subject_mc_id_pk
group by t2.kdm_mdid_pk,t2.kdm_title