1.使用正则表达式进行排重
SELECT regexp_replace('2,2,3,4,5 ', '([^,]+)(,\1)+', '\1') AS col
FROM dual
查询结果：
- SQL> SELECT regexp_replace('2,2,3,4,5 ', '([^,]+)(,\1)+', '\1') AS col
- 2 FROM dual
- 3 ;
-
- COL
- --------
- 2,3,4,5
可以对字符串进行排重
2. 使用上面的正则表达式对listagg进行排重
- SELECT listagg(t.deptno, ',') within GROUP(ORDER BY t.deptno) deptno,
- regexp_replace((listagg(t.deptno, ',') within GROUP(ORDER BY t.deptno)), '([^,]+)(,\1)+', '\1') bl_number
- FROM scott.emp t
执行结果：
- SQL> SELECT listagg(t.deptno, ',') within GROUP(ORDER BY t.deptno) deptno,
- 2 regexp_replace((listagg(t.deptno, ',') within GROUP(ORDER BY t.deptno)), '([^,]+)(,\1)+', '\1') bl_number
- 3 FROM scott.emp t
- 4 ;
-
- DEPTNO BL_NUMBER
- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
- 10,10,10,20,20,20,20,20,30,30,30,30,30,30 10,20,30
成功对listagg排重
|
发表于 2020-7-8 18:25:01
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
楼主
