构建测试表：
Sql代码 create table TABLE1 ( ID INTEGER, NAME VARCHAR2(10) ) create table TABLE2 ( ID INTEGER, ROLE VARCHAR2(10) ) insert into TABLE1 (ID, NAME) values (1, '张三'); insert into TABLE1 (ID, NAME) values (2, '李四'); commit; insert into TABLE2 (ID, ROLE) values (1, '查询'); insert into TABLE2 (ID, ROLE) values (1, '分析'); insert into TABLE2 (ID, ROLE) values (1, '决策'); insert into TABLE2 (ID, ROLE) values (2, '查询'); commit; create table TABLE1 ( ID INTEGER, NAME VARCHAR2(10) ) create table TABLE2 ( ID INTEGER, ROLE VARCHAR2(10) ) insert into TABLE1 (ID, NAME) values (1, '张三'); insert into TABLE1 (ID, NAME) values (2, '李四'); commit; insert into TABLE2 (ID, ROLE) values (1, '查询'); insert into TABLE2 (ID, ROLE) values (1, '分析'); insert into TABLE2 (ID, ROLE) values (1, '决策'); insert into TABLE2 (ID, ROLE) values (2, '查询'); commit;
要求输出结果：
Sql代码 ID NAME ROLE 1 张三 查询,分析,决策 2 李四 查询 ID NAME ROLE 1 张三 查询,分析,决策 2 李四 查询
方法一、使用wmsys.wm_concat
Sql代码 select table1.*,wmsys.wm_concat(role) from table1,table2 where table1.id=table2.id group by table1.id,table1.name select table1.*,wmsys.wm_concat(role) from table1,table2 where table1.id=table2.id group by table1.id,table1.name
方法二、使用sys_connect_by_path
Sql代码 select id, name, ltrim(max(sys_connect_by_path(role, ',')), ',') from (select row_number() over(partition by table1.id order by name) rn,table1.*, role from table1, table2 where table1.id = table2.id) start with rn = 1 connect by prior rn = rn - 1 and prior id = id group by id, name order by id select id, name, ltrim(max(sys_connect_by_path(role, ',')), ',') from (select row_number() over(partition by table1.id order by name) rn,table1.*, role from table1, table2 where table1.id = table2.id) start with rn = 1 connect by prior rn = rn - 1 and prior id = id group by id, name order by id
方法三、使用自定义函数
Sql代码 create or replace function my_concat(mid in integer) return varchar2 --记住：参数和返回值里的数据类型都不用定义长度 is result varchar2(4000); --定义变量，记住Oracle中定义变量不需要 begin for temp_cursor in (select role from table2 where id=mid) loop --此处在游标FOR循环中使用查询 result :=result || temp_cursor.role || ','; --Oracle中字符连接使用||，而sql server中用+ end loop; result := rtrim(result,','); --去掉最后一个空格,还有Oracle中的赋值前面没有set return result; end; select table1.*,my_concat(table1.id) from table1,table2 where table1.id=table2.id group by table1.id,table1.name order by table1.id create or replace function my_concat(mid in integer) return varchar2 --记住：参数和返回值里的数据类型都不用定义长度 is result varchar2(4000); --定义变量，记住Oracle中定义变量不需要 begin for temp_cursor in (select role from table2 where id=mid) loop --此处在游标FOR循环中使用查询 result :=result || temp_cursor.role || ','; --Oracle中字符连接使用||，而sql server中用+ end loop; result := rtrim(result,','); --去掉最后一个空格,还有Oracle中的赋值前面没有set return result; end; select table1.*,my_concat(table1.id) from table1,table2 where table1.id=table2.id group by table1.id,table1.name order by table1.id
转载于:https://www.cnblogs.com/zhahost/archive/2008/12/03/1346706.html
相关资源：数据结构—成绩单生成器