来自：SQL-字符串连接聚合函数
问题:
userId role_name role_id
1 管理员 1
1 编辑 2
1 超级管理员 3
2 普通会员 4
3 高级会员 5
4 null null
很简单的数据，就是一个用户可能会对应多个角色，现在要求能用程序或者sql的方式用户和角色逗号进行分割。要求得到的数据如下:
userId role_name
1 管理员,编辑,超级管理员
2 普通会员
3 高级会员
4 null
解答:
用程序估计是再简单不过了，数据库的话如下：
MySQL:直接调用函数group_contact完成
oracle10g:直接掉函数wm_concat完成
oracle9i:麻烦点，不过主要是通过sys_connect_by_path，这个函数能树枝进行按指定字符连接，之所以产生树是因为这里面用到了Oracle的START WITH 递归成树的
(1) MySQL分组字符串拼接
group_concat函数是典型的字符串连接函数，下面就为您介绍MySQL group_concat的语法，希望对您学习
MySQL group_concat函数有所帮助。
MySQL group_concat函数
完整的语法如下：
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
我的理解就是: 后面的 group by xx 分好组之后, 然后将组内的数据连接起来,默认是逗号
测试sql语句:
insert into role VALUES(1,1,'管理员',1);
insert into role VALUES(2,1,'编辑',2);
insert into role VALUES(3,1,'超级管理员',3);
insert into role VALUES(4,2,'普通会员',4);
insert into role VALUES(5,3,'高级会员',5);例子:
select userId,group_concat(role_name,';') from role group by userId;
result:
1 管理员;,编辑;,超级管理员;
2 普通会员;
3 高级会员;
(1) oracle分组字符串拼接
select grp, wmsys.wm_concat(str)
from (select 1 grp, 'a1' str
from dual
union
select 1 grp, 'a2' str
from dual
union
select 2 grp, 'b1' str
from dual
union
select 2 grp, 'b2' str
from dual
union
select 2 grp, 'b3' str from dual) t
group by grp执行效果：
原始数据 | 分组聚合后 |
|
|
注意事项：
但是这有三个问题： 参考：
1. wmsys.wm_concat 是10g才有的，以前的版本无法使用
2. wmsys.wm_concat 是ORACLE内部函数，没有对外公布，也就是说，你可以使用，但是如果发生什么问题ORACLE概不负责。最显然的是ORACLE版本从10.2.0.4升级到10.2.0.5，只是一个小版本的变更，足以让你的系统出现问题。
解决方案：
1. 升级到Oracle 11g Release 2，此版本引入了LISTAGG 函数，使得聚集连接字符串变得很容易，并且允许使用我们指定连接串中的字段顺序。
2. 用自己定义的聚合函数替换wmsys.wm_concat
代码如下：
-- 1. 建立测试表和数据：
CREATE TABLE WM_TEST
(
CODE INTEGER,
NAME VARCHAR2(20 BYTE)
);
Insert into WM_TEST (CODE, NAME) Values (1, 'a');
Insert into WM_TEST (CODE, NAME) Values (1, 'b');
Insert into WM_TEST (CODE, NAME) Values (1, 'c');
Insert into WM_TEST (CODE, NAME) Values (2, '中');
Insert into WM_TEST (CODE, NAME) Values (2, '国');
Insert into WM_TEST (CODE, NAME) Values (2, '人');
COMMIT;
-- 2. 建立自定义聚合函数
CREATE OR REPLACE TYPE ConcatObj AS OBJECT
(
fieldValue VARCHAR2 (4000),
separator VARCHAR2 (100)
)
/
CREATE OR REPLACE TYPE type_wm_concat
AS OBJECT
(
l_join_str VARCHAR2 (32767 BYTE), -- 连接后的字符串
l_flag VARCHAR2 (100 BYTE), -- 分隔符，默认值可在body中定义
STATIC FUNCTION ODCIAggregateInitialize -- 初始化
(sctx IN OUT type_wm_concat)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate -- 迭代器，处理每行数据
(self IN OUT type_wm_concat,
VALUE IN ConcatObj)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate -- 迭代结束后处理代码
(self IN OUT type_wm_concat,
return_v OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge -- 结果合并
(self IN OUT type_wm_concat,
ctx2 IN type_wm_concat)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY type_wm_concat
IS
STATIC FUNCTION ODCIAggregateInitialize -- 初始化
(sctx IN OUT type_wm_concat)
RETURN NUMBER
IS
BEGIN
sctx := type_wm_concat (NULL, NULL);
RETURN ODCIConst.success;
END ODCIAggregateInitialize;
MEMBER FUNCTION ODCIAggregateIterate -- 迭代器，处理每行数据
(self IN OUT type_wm_concat,
VALUE IN ConcatObj)
RETURN NUMBER
IS
BEGIN
IF self.l_join_str IS NOT NULL AND VALUE.fieldValue IS NOT NULL
THEN
self.l_join_str := self.l_join_str || self.l_flag || VALUE.fieldValue;
ELSIF VALUE.fieldValue IS NOT NULL
THEN
self.l_join_str := VALUE.fieldValue;
self.l_flag := VALUE.separator;
END IF;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate -- 迭代结束后处理代码
(self IN OUT type_wm_concat,
return_v OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER
IS
BEGIN
return_v := self.l_join_str;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge (self IN OUT type_wm_concat,
ctx2 IN type_wm_concat)
RETURN NUMBER
IS
BEGIN
IF ctx2.l_join_str IS NOT NULL AND self.l_join_str IS NOT NULL
THEN
self.l_join_str := self.l_join_str || self.l_flag || ctx2.l_join_str;
ELSIF ctx2.l_join_str IS NOT NULL
THEN
self.l_join_str := ctx2.l_join_str;
END IF;
RETURN ODCIConst.Success;
END;
END;
/
-- 3. 封装为一个普通的SQL函数：
CREATE OR REPLACE FUNCTION my_wm_concat (pi_str ConcatObj)
RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE USING type_wm_concat;
/
-- 4. 测试：
SELECT wt.code, my_wm_concat (ConcatObj (wt.name, '|+=')) names
FROM wm_test wt
GROUP BY wt.code;code name
1 a|+=b|+=c
2 中|+=国|+=人
