Oracle合并列,且合并内容按指定列排序_wm_concat和listagg

小灰灰 2024-03-30 09:37 66阅读 0赞

#### 文章目录 ####

*  Oracle合并列,且合并内容按指定列排序\_wm\_concat和listagg
 *   *  测试表结构和数据准备
     *  1. wm\_concat
     *   *  1-2 wm\_concat方法实现：
     *  2. listagg函数实现(推荐使用，效率高、用法简单)：
     *  3. 最终实现效果如下图示：

## Oracle合并列,且合并内容按指定列排序\_wm\_concat和listagg ##

说明： 直接使用wm_concat或listagg函数拼接字段，拼接后的值顺序是无规则的；

### 测试表结构和数据准备 ###

create table tb(
       seq number(10),
       remark varchar2(100),
       createtime date
    );
    insert into tb(seq, remark, createtime)
    select 1, '说明1', to_date('20210101','yyyymmdd') from dual
    union all
    select 1, '说明2', to_date('20210111','yyyymmdd') from dual
    union all
    select 1, '说明3', to_date('20210201','yyyymmdd') from dual
    union all
    select 1, '说明4', to_date('20210208','yyyymmdd') from dual
    union all
    select 5, '说明5', to_date('20210301','yyyymmdd') from dual
    ;
    -- 表数据内容如下图所示：

![在这里插入图片描述][828d4bf66342422391377dd9c40e3ae3.png_pic_center]

### 1. wm\_concat ###

注： Oracle12及以上该函数弃用了，可以用listagg替代，
    	下方第2点会讲到，如仍要使用该函数，可参考如下Sql内容手动创建！

-- 创建wm_concat函数
    -- 首先使用dba账号登录oracle数据库
    -- 解锁wmsys用户
    alter user wmsys account unlock;
    
    -- 并为wmsys用户授权，可根据需要授权，不建议授权所有权限
    grant all privileges to wmsys;
    
    -- 如果不知道wmsys用户的密码，可以修改其密码
    alter user wmsys identified by 123456;
    
    -- 使用wmsys用户登录数据库
    conn wmsys/123456
    
    -- 在wmsys下创建可用的wm_concat函数，直接执行以下语句
    
    -- 定义类型
    CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT
    (
    CURR_STR VARCHAR2(32767),
    STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
    MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
    P1 IN VARCHAR2) RETURN NUMBER,
    MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
    RETURNVALUE OUT VARCHAR2,
    FLAGS IN NUMBER)
    RETURN NUMBER,
    MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
    SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
    );
    /
    
    -- 定义类型body:
    CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL
    IS
    STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
    RETURN NUMBER
    IS
    BEGIN
    SCTX := WM_CONCAT_IMPL(NULL) ;
    RETURN ODCICONST.SUCCESS;
    END;
    MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
    P1 IN VARCHAR2)
    RETURN NUMBER
    IS
    BEGIN
    IF(CURR_STR IS NOT NULL) THEN
    CURR_STR := CURR_STR || ',' || P1;
    ELSE
    CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
    END;
    MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
    RETURNVALUE OUT VARCHAR2,
    FLAGS IN NUMBER)
    RETURN NUMBER
    IS
    BEGIN
    RETURNVALUE := CURR_STR ;
    RETURN ODCICONST.SUCCESS;
    END;
    MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
    SCTX2 IN WM_CONCAT_IMPL)
    RETURN NUMBER
    IS
    BEGIN
    IF(SCTX2.CURR_STR IS NOT NULL) THEN
    SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;
    END IF;
    RETURN ODCICONST.SUCCESS;
    END;
    END;
    /
    --自定义行变列函数:
    CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2)
    RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ;
    /
    
    -- 创建完成，给其创建同义词及授权，以供其他用户能正常使用。
    create public synonym WM_CONCAT_IMPL for wmsys.WM_CONCAT_IMPL
    /
    create public synonym wm_concat for wmsys.wm_concat / grant execute ON wm_concat_impl to public
    /
    GRANT EXECUTE ON wm_concat TO PUBLIC
    /

#### 1-2 wm\_concat方法实现： ####

-- 说明：使用wm_contat方法实现 ，借助over(partition by xx order by ...实现)
    -- 		wm_concat over(partition by 分组字段 order by 排序字段)后，
    -- 		外面再套max和group by 查询
    
    
    select seq, max(remark) remark
      from (select seq,
                   wm_concat('[' || to_char(createtime, 'yyyy-mm-dd') || ']' || remark) over(partition by seq order by createtime) remark
              from tb
            )
     group by seq;

### 2. listagg函数实现(推荐使用，效率高、用法简单)： ###

-- Oracle 11g及以上版本，使用新增的函数listagg更加便捷高效！
    -- 		2个参数含义：第1个参数为需要拼接的字段，第2个参数为分隔符（可选）
    
    select seq,
           listagg('[' || to_char(createtime, 'yyyy-mm-dd') || ']' || remark, ',') within group(order by createtime) remark
      from tb
     group by seq;

### 3. 最终实现效果如下图示： ###

![在这里插入图片描述][6e918e1c011b4e9da5c603184bc68ff1.png_pic_center]

[828d4bf66342422391377dd9c40e3ae3.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/f9f37696f7f546adb60d1c00928ef63e.png
[6e918e1c011b4e9da5c603184bc68ff1.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/62a27d7efedc4a29b6f7beef7a578a59.png