我有个信息要连接起来。但不知道如何操作。具体如下,还望帮忙看下,感谢!有个类似这样的信息
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呢?
解决方案 »
- mssql中的chrtran等同于oracle中的相应的函数
- Composite Range-Hash Partitioning 释疑
- pl sql 示例错误 ORA-00900: invalid SQL statement !
- 存储过程自动消失的问题?
- 高分求教关于数据表的设计问题
- 这是我去某公司应聘的ORACLE试题,大家帮忙帮忙解答)
- 有可能吗,SQL要得到这样的结果?
- 请教:在sqwplusw和emc输入用户名口令后,提示ORA-12541:TNS:没有监听器,是什么原因造成的。急~
- 请教Oracle字符集转换问题。。。问题紧急!请高手执教。
- 求助,一个关于连接oracle的问题,涉及到网络问题,请来看看,急!!!!
- 请教大家:如何在存储过程中创建,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 朱梁,李涛,韩丰
这个很容易解决