例如我有一个表
表a:
id name
1 q1
2 q1
3 q3要写条语句查询显示为
id name
1 2 q1
3 q3
表a:
id name
1 q1
2 q1
3 q3要写条语句查询显示为
id name
1 2 q1
3 q3
解决方案 »
- 频繁进行插入提交 报ORA-24337错误
- 请教达人:往数据库中要插入1亿多条数据用JAVA程序实现可行吗?
- 为什么使用SQL*Plus Worksheet执行带有替换变量的SQL语句会出错
- SQL与PL/SQL有什么区别,什么时候用SQL,什么时候用PL/SQL
- oracle pl/sql判断时间差的问题
- [菜鸟提问]关于存储过程的一个小问题(100分急盼)
- 在PL/SQL中定义一个Varchar2 变量咋成了未定义的
- 怎样将oracle数据库状态自动设为打开?
- 怎么取得ORACLE数据库表中一个连续的序列字段中最小的空号
- 字段更新问题
- 手动创建数据库的时候报错,结果导致整个ORACEL进程全部关闭 (linux系统下,oracle11g)
- Oracle Directory Manager 连接问题
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