表结构式这样的:
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表空间的问题
- 急急急。。。在.net2.0环境下,在一个事物中锁行锁住多个表,锁表时出错不全部回滚,只想回滚但前出错的那些操作。
- 求助解决where后使用or查询慢的问题
- 设置ODBC数据源添加Oracle驱动程序时 测试Test connection 出错
- 删除2个表中相同的记录
- ORACLE中的模糊查询问题。
- 为什么我在windows2003下,安装不了oracle 9i
- 如果修改oracle中日期类型的默认格式!!在先等待,解决就揭贴
- 怎样统计字段field1(类型为LONG)不为空的那些记录?
- 有用过多备份进行网站数据备份的吗?
- 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显然不是这么个字段