求助,工作中遇见一个问题:
表t1
id name
1 a
1 b
2 a
2 c
2 d
转成
表t2
id name
1 a,b
2 a,c,d
请大家指教啊!!!多谢
注意不能用sys_connect_by_path,因为在connect by 的时候由于层次太多了,有4000层,导致性能不能满足。
我原来的写法是:
select v.id,substr(MAX(sys_connect_by_path(v.name, '|')), 2) as name
from (select t1.id,row_number() over(PARTITION BY t1.id ORDER BY t1.name)rn from t1)v start with v.rn = 1 connect by v.id= PRIOR v.id
and v.rn - 1 = PRIOR v.rn GROUP BY v.id
表t1
id name
1 a
1 b
2 a
2 c
2 d
转成
表t2
id name
1 a,b
2 a,c,d
请大家指教啊!!!多谢
注意不能用sys_connect_by_path,因为在connect by 的时候由于层次太多了,有4000层,导致性能不能满足。
我原来的写法是:
select v.id,substr(MAX(sys_connect_by_path(v.name, '|')), 2) as name
from (select t1.id,row_number() over(PARTITION BY t1.id ORDER BY t1.name)rn from t1)v start with v.rn = 1 connect by v.id= PRIOR v.id
and v.rn - 1 = PRIOR v.rn GROUP BY v.id
解决方案 »
- 关于用right join or left join补全的问题?
- 存储过程嵌套游标替换表中的值
- 连接数据库,sql操作异常
- 去重复报ORA-00913: too many values 。。。
- 能否推荐一本数据库原理的书
- oracle 未写入文档的参数介绍
- 触发器问题!
- #########关于CONNECT的简单问题#########
- 什么是Instance?Instance Manager它怎么管理?我是初学者,好不多东西不懂,以
- Oracle8i客户端连接Oracle8.0或者更低版本数据库问题(在线等待)
- 一个气的吐血的问题,请各位大虾指教(sql语句可以执行,但是存储过程不能执行)
- oracle 不同库,或不同用户之间如何互相访问???
create table tbale1(no int, content varchar2(100));
insert into tbale1
select 1,'aa' from dual union all
select 1,'bb' from dual union all
select 1,'cc' from dual union all
select 2,'mm' from dual union all
select 2,'nn' from dual union all
select 3,'oo' from dual;
--建立函数
create or replace function sum_string(v_sql varchar2)
return varchar2
as
type cur_alldata is ref cursor;
l_alldata cur_alldata;
v_row varchar2(99);
v_sum varchar2(3999);
begin
open l_alldata for v_sql;
loop
fetch l_alldata into v_row;
exit when l_alldata%notfound;
v_sum := v_sum||','||v_row;
end loop;
v_sum := substr(v_sum,2);
close l_alldata;
return v_sum;
end;
--执行查询
select distinct no,sum_string('select content from tbale1 where no='''||no||''' group by no,content') from tbale1
--执行结果
1 aa,bb,cc
2 mm,nn
3 oo
请教一条SQL
NAME USERID
张三 KB001
张三 KB002
李四 KB001
李四 KB002
李四 KB003我想用SQL实现表示如下:
NAME USERID
张三 KB001,KB002
李四 KB001,KB002,KB003select cola,rtrim(
max(decode(colb,'KB001',colb||',',null))||
max(decode(colb,'KB002',colb||',',null))||
max(decode(colb,'KB003',colb||',',null)),',')
from test
group by cola
/COLA RTRIM(MAX(DECODE(COLB,'KB001',COL
---------- ---------------------------------
李四 KB001,KB002,KB003
张三 KB001,KB002
With x As (
SELECT '张三' NAME, 'KB001' userid FROM dual UNION ALL
SELECT '张三' NAME, 'KB002' userid FROM dual UNION ALL
SELECT '李四' NAME, 'KB001' userid FROM dual UNION ALL
SELECT '李四' NAME, 'KB002' userid FROM dual UNION ALL
SELECT '王五' NAME, 'KB006' userid FROM dual UNION ALL
SELECT '王五' NAME, 'KB002' userid FROM dual UNION ALL
SELECT '李四' NAME, 'KB003' userid FROM dual)
SELECT NAME, ltrim(MAX(sys_connect_by_path(userid, ',')), ',') userid
FROM (SELECT NAME, userid, row_number() over(PARTITION BY NAME ORDER BY userid) rn, rownum prn FROM x)
START WITH rn = 1
CONNECT BY prn - 1 = PRIOR prn AND NAME = PRIOR NAME
GROUP BY NAME
ORDER BY NAME;结果:
NAME USERID
1 李四 KB001,KB002
2 王五 KB002
3 张三 KB001,KB002
这里的“王五”信息不全。with x AS (
SELECT '张三' NAME, 'KB101' userid FROM dual UNION ALL
SELECT '张三', 'KB002' FROM dual UNION ALL
SELECT '张三', 'KB113' FROM dual UNION ALL
SELECT '张三', 'KB003' FROM dual UNION ALL
SELECT '张三', 'KB004' FROM dual UNION ALL
SELECT '张三', 'KB005' FROM dual UNION ALL
SELECT '张三', 'KB013' FROM dual UNION ALL
SELECT '张三', 'KB103' FROM dual UNION ALL
SELECT '李四', 'KB001' FROM dual UNION ALL
SELECT '李四', 'KB002' FROM dual UNION ALL
SELECT '王五', 'KB002' FROM dual UNION ALL
SELECT '王五', 'KB006' FROM dual UNION ALL
SELECT '李四', 'KB003' FROM dual)
--//--直接提取法
SELECT NAME, ltrim(MAX(sys_connect_by_path(userid, ',')), ',') userid
FROM (SELECT NAME, userid, row_number() over(PARTITION BY NAME ORDER BY userid) rn FROM x)
START WITH rn = 1 CONNECT BY rn - 1 = PRIOR rn AND NAME = PRIOR NAME GROUP BY NAME ORDER BY NAME;