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

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
;
-- 表数据内容如下图所示：

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. 最终实现效果如下图示：