sqlite 连接字符串 自动创建 sql 连接字符串函数

来自：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    中|+=国|+=人