Oracle_Oracle去重分组拼接字符串

要实现的是去重按顺序分组拼接字段，且输出表中需要拼接多个字段。

1、查了网上大概有四种方法，各有特点：

1、wmsys.wm_concat(column)

2、listagg (column,[,]) within group (order by ) [over (partition by  )]

3、sys_connect_by_path(column,<分隔符>)

4、xmlagg (content column,[,] wellformed) within group (order by ) [over (partition by  )]

第1种，WMSYS用户用于Workspace Manager，函数对象可能因版本而不同，Oracle官方也不建议使用；不可指定分隔符；支持去重。

第2种，listagg返回结果varchar2类型（最大长度4000），当拼接字符串过长会提示“返回结果为字符串连接的结果过长”；可指定分隔符；不支持去重。

第3种，此方法未测试。

第4种，返回结果为clob（Character Large Object）类型，最大容量为4GB；可指定分隔符；不支持去重。

2、确定了使用第4种方法，发现实现难点在于分组结果去重，又存在多个字段需要拼接，则先去重只可针对一个字段，多个字段就存在问题，采用先产生针对不同字段拼接的临时表，使用分组字段作为连接更新到主表中。

网上针对xmlagg可使用的去重方法有两种，一种是使用正则表达式（适用于字符串大小比较小的情况，不适用），另一种是先去重再聚合。

CREATE Global Temporary TABLE D302_2 ON COMMIT PRESERVE ROWS AS
SELECT SHENG,
       XIAN,
       XMMC,
       BHYY,
       BHSD,
       SYLDXZ,
       rtrim(xmlagg(xmlparse(content PAN_NO_TB || '、' wellformed) ORDER BY PAN_NO_TB).getclobval(),
             '、') AS PAN_NO_TB
  FROM (SELECT DISTINCT SHENG, XIAN, XMMC, BHYY, BHSD, SYLDXZ, PAN_NO_TB
          FROM TEST
         WHERE PAN_NO_TB <> 0)
 GROUP BY SHENG, XIAN, XMMC, BHYY, BHSD, SYLDXZ;

from 后面括号内实现去重目的

group by 实现分组目的

xmlagg(xmlparse(content PAN_NO_TB || '、' wellformed) ORDER BY PAN_NO_TB) 实现拼接

getclobval() 获得clob字符串

rtrim 去除最后面的 "、"

注意：xmlagg字段若为字符串，需适应to_char()函数转换。

3、使用分组字段作为连接更新到主表中（a2,a3,a7,a8,a9,a10分别为主表的SHENG, XIAN, XMMC, BHYY, BHSD, SYLDXZ）

UPDATE D302_1
   SET D302_1.a5 =
       (SELECT PAN_NO_TB
          FROM D302_3
         WHERE D302_3.sheng = D302_1.A2
           AND D302_3.XIAN = D302_1.A3
           AND D302_3.XMMC = D302_1.A7
           AND D302_3.BHYY = D302_1.A8
           AND D302_3.BHSD = D302_1.A9
           AND D302_3.SYLDXZ = D302_1.A10)
 WHERE EXISTS (SELECT PAN_NO_TB
          FROM D302_3
         WHERE D302_3.sheng = D302_1.A2
           AND D302_3.XIAN = D302_1.A3
           AND D302_3.XMMC = D302_1.A7
           AND D302_3.BHYY = D302_1.A8
           AND D302_3.BHSD = D302_1.A9
           AND D302_3.SYLDXZ = D302_1.A10);

WHERE EXISTS避免不匹配项被更新为空值。