我有个信息要连接起来。但不知道如何操作。具体如下,还望帮忙看下,感谢!有个类似这样的信息
table1a列 b列
1 王立
1 张三
1 张晓
2 石磊
2 王鑫
2 金鹏
3 李涛
3 韩丰
3 朱梁我想得到这样的结果
1 王立,张三,张晓
2 石磊,王鑫,金鹏
3 李涛,韩丰,朱梁如何写sql呢?
table1a列 b列
1 王立
1 张三
1 张晓
2 石磊
2 王鑫
2 金鹏
3 李涛
3 韩丰
3 朱梁我想得到这样的结果
1 王立,张三,张晓
2 石磊,王鑫,金鹏
3 李涛,韩丰,朱梁如何写sql呢?
解决方案 »
- ORACLE数据库导入问题
- 100分 求sql
- 【急急急急急急问】Oracle 9i怎么备份?????
- 急,通过Oracle 的数据文件能否象 MSSQL2000那样附加吗?
- ora-12537:tns:连接关闭
- oracle9i(第二版)监听服务不能启动(操作系统:windows server 2003)
- 显示中文的问题
- oracle9i安装以后需要什么配置吗?谢谢~
- 一个时间段语句怎么写啊?在线等待
- 在oracle8i中有没有类似于8.0.5的Storage Manager的工具?
- 请教大家:如何在存储过程中创建,SELECT,INSERT,然后再DROP掉这个表呢?
- 访问db_link的表好慢,有什么优化的设置或方法没?
SELECT t.a, MAX(substr(sys_connect_by_path(t.b, ','), 2)) str
FROM (SELECT a, b, row_number() over(PARTITION BY a ORDER BY b) rn
FROM table1) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND a = PRIOR a
GROUP BY t.a;
select 1 a,'王立' b from dual union all
select 1, '张三' from dual union all
select 1, '张晓' from dual union all
select 2, '石磊' from dual union all
select 2, '王鑫' from dual union all
select 2, '金鹏' from dual union all
select 3, '李涛' from dual union all
select 3, '韩丰' from dual union all
select 3, '朱梁' from dual)
--以上为提供数据的语句
select a,replace(wm_concat(b),';',',') newb from tb
group by a A NEWB
---------- --------------------
1 王立,张三,张晓
2 石磊,王鑫,金鹏
3 李涛,朱梁,韩丰
-2l不是给你提供了吗
SQL>
SQL> with table1 as(
2 select 1 a,'王立' b from dual union all
3 select 1, '张三' from dual union all
4 select 1, '张晓' from dual union all
5 select 2, '石磊' from dual union all
6 select 2, '王鑫' from dual union all
7 select 2, '金鹏' from dual union all
8 select 3, '李涛' from dual union all
9 select 3, '韩丰' from dual union all
10 select 3, '朱梁' from dual)
11 SELECT t.a, MAX(substr(sys_connect_by_path(t.b, ','), 2)) str
12 FROM (SELECT a, b, row_number() over(PARTITION BY a ORDER BY b) rn
13 FROM table1) t
14 START WITH rn = 1
15 CONNECT BY rn = PRIOR rn + 1
16 AND a = PRIOR a
17 GROUP BY t.a; A STR
---------- --------------------------------------------------------------------------------
1 王立,张三,张晓
2 金鹏,石磊,王鑫
3 韩丰,李涛,朱梁SQL>
with tb as(
select 1 a,'王立' b from dual union all
select 1, '张三' from dual union all
select 1, '张晓' from dual union all
select 2, '石磊' from dual union all
select 2, '王鑫' from dual union all
select 2, '金鹏' from dual union all
select 3, '李涛' from dual union all
select 3, '韩丰' from dual union all
select 3, '朱梁' from dual)
select a,max(b1) b1
from (select a,ltrim(sys_connect_by_path(b,','),',') b1
from (select a,b,row_number() over(partition by a order by b) rn
from tb)
connect by prior rn=rn+1 and connect_by_root(a)=a)
group by aSQL> with tb as(
2 select 1 a,'王立' b from dual union all
3 select 1, '张三' from dual union all
4 select 1, '张晓' from dual union all
5 select 2, '石磊' from dual union all
6 select 2, '王鑫' from dual union all
7 select 2, '金鹏' from dual union all
8 select 3, '李涛' from dual union all
9 select 3, '韩丰' from dual union all
10 select 3, '朱梁' from dual)
11 select a,max(b1) b1
12 from (select a,ltrim(sys_connect_by_path(b,','),',') b1
13 from (select a,b,row_number() over(partition by a order by b) rn
14 from tb)
15 connect by prior rn=rn+1 and connect_by_root(a)=a)
16 group by a
17
SQL> /
A B1
---------- --------------------------------------------------------------------------------
1 张晓,张三,王立
2 王鑫,石磊,金鹏
3 朱梁,李涛,韩丰
这个很容易解决