表结构式这样的:
CREATE TABLE t_row_str(
ID INT,
col VARCHAR2(10));
INSERT INTO t_row_str VALUES(1,'a');
INSERT INTO t_row_str VALUES(2,'b');
INSERT INTO t_row_str VALUES(3,'c');
INSERT INTO t_row_str VALUES(4,'a');
INSERT INTO t_row_str VALUES(5,'d');
INSERT INTO t_row_str VALUES(6,'e');
INSERT INTO t_row_str VALUES(7,'c');
COMMIT;
SELECT * FROM t_row_str;多行转字符串是这样的:
适用范围:8i,9i,10g及以后版本
SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','), 2)) str
FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
FROM t_row_str) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id
GROUP BY t.id;
问题是这个方法转化后为什么每个字符串只有3个字符? 怎么改才能够让所有行转化为一个字符串?? 100分求教
CREATE TABLE t_row_str(
ID INT,
col VARCHAR2(10));
INSERT INTO t_row_str VALUES(1,'a');
INSERT INTO t_row_str VALUES(2,'b');
INSERT INTO t_row_str VALUES(3,'c');
INSERT INTO t_row_str VALUES(4,'a');
INSERT INTO t_row_str VALUES(5,'d');
INSERT INTO t_row_str VALUES(6,'e');
INSERT INTO t_row_str VALUES(7,'c');
COMMIT;
SELECT * FROM t_row_str;多行转字符串是这样的:
适用范围:8i,9i,10g及以后版本
SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','), 2)) str
FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
FROM t_row_str) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id
GROUP BY t.id;
问题是这个方法转化后为什么每个字符串只有3个字符? 怎么改才能够让所有行转化为一个字符串?? 100分求教
解决方案 »
- 如何删除表中所有相同记录?
- 请教个oracle的小问题
- 急 oracle数据库 出现ORA-00907: 缺少右括号 在线等
- 两道用SQL语句查询的题目 高手帮忙做一下 谢谢了
- oracle 中怎么在表中加上行的权限?
- 请问如何将数据库从SQL SERVER 2000 迁移到ORACLE 8中去?
- 我想安装Oracle9i在我的计算机上,为什么安装时提示 areasqueries 错误?
- 请教问题,急,在线等!谢谢
- 请高手指点---oracle无法启动?(在线等待)
- 急!在企业管理器创建一个导出表的作业,出现“VNI2015:验证错误”,怎摸解决?
- Java 调用存储过程批量更新问题
- 連接問題
最后输出 '1,2,3,4,5,6', 'a,b,c,d,e,f'?
(
select decode(max(rn),1,'a','') a ,decode(max(rn),2,'b','') b,decode(max(rn),3,'c','') c,decode(max(rn),4,'d','') d,decode(max(rn),5,'e','') e
from
(
select rownum rn , col from
(
select distinct col from t_row_str order by 1
)
)
group by col
)
select wm_concat(col) from t_row_str order by col;
SQL> SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','), 2)) str
2 FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
3 FROM t_row_str) t
4 START WITH rn = 1
5 CONNECT BY rn = PRIOR rn + 1
6 AND id = PRIOR id
7 GROUP BY t.id; ID STR
---------- --------------------
1 a
2 b
3 c
4 a
5 d
6 e
7 c已选择7行。
SQL> select wm_concat(f.str) from (SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','),2)) str
2 FROM (SELECT id, col, row_number() over(PARTITION BY col ORDER BY col) rn
3 FROM t_row_str) t
4 START WITH rn = 1
5 CONNECT BY rn = PRIOR rn + 1
6 AND id = PRIOR id
7 GROUP BY t.id) f order by f.str;WM_CONCAT(F.STR)
--------------------------------------------------------------------------------
a,b,c,d,e
---------- ----------
1 a
2 b
1 a
1 b
1 c
2 a
2 d
2 e
2 c9 rows selected.[TEST@ora10gr1#2009-12-18/18:09:38] SQL>SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','),
2 FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
3 FROM t_row_str) t
4 START WITH rn = 1
5 CONNECT BY rn = PRIOR rn + 1
6 AND id = PRIOR id
7 GROUP BY t.id; ID STR
---------- ----------
1 a,a,b,c
2 a,b,c,d,e
FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
FROM t_row_str) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id
GROUP BY t.id;
上面这个查询是没有问题的
如果按照楼主给出的数据1,2,3,4,5,6,7那么上面的查询语句是看不出效果的
因为在OVER窗口函数中指定了partition by id,可你的7条记录7个id都不一样 所以感觉没什么效果
重新建几条数据你看看
SQL> select * from t_row_str; ID COL
---------- ----------
1 a
1 b
1 c
2 a
2 d
2 e
3 c7 rows selected.
SQL> SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','), 2)) str
2 FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
3 FROM t_row_str) t
4 START WITH rn = 1
5 CONNECT BY rn = PRIOR rn + 1
6 AND id = PRIOR id
7 GROUP BY t.id;
ID STR
---------- ------------------------------
1 a,b,c
2 a,d,e
3 c
select wm_concat(distinct t.col)
from t_row_str t9i
SELECT MAX(substr(sys_connect_by_path(t.col, ','), 2)) str
FROM (SELECT distinct col, dense_rank() over(ORDER BY col) rn
FROM t_row_str) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1 可能你需要根据某个字段来分组,但这里的id显然不是这么个字段