Oracle 多行记录合并/连接/聚合字符串(合并内容)

来源：互联网发布：淘宝一件代发货源网免费加盟代理编辑：程序博客网时间：2024/05/29 17:27

什么是合并多行字符串（连接字符串）呢，例如：
SQL> desc test;
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
COUNTRY VARCHAR2(20) Y
CITY VARCHAR2(20) Y

SQL> select * from test;

COUNTRY CITY
-------------------- --------------------
中国台北
中国香港
中国上海
日本东京
日本大阪
要求得到如下结果集：
------- --------------------
中国台北,香港,上海
日本东京，大阪
实际就是对字符实现一个聚合功能。

SELECT country,max(substr(city,2)) city
FROM
(SELECT country,sys_connect_by_path(city,',') city
FROM
(SELECT country,city,country||rn rchild,country||(rn-1) rfather
FROM
(SELECT test.country ,test.city,row_number() over (PARTITION BY test.country ORDER BY test.city) rn FROM test))
CONNECT BY PRIOR rchild=rfather START WITH rfather LIKE '%0')
GROUP BY country;
下面分步解析，有4个FROM，就有4次结果集的操作。
step 1 给记录加上序号rn
SQL> SELECT test.country ,test.city,row_number() over (PARTITION BY test.country ORDER BY test.city) rn
2 FROM test
3 /

COUNTRY CITY RN
-------------------- -------------------- ----------
日本大阪 1
日本东京 2
中国上海 1
中国台北 2
中国香港 3
step 2 创造子节点父节点
SQL> SELECT country,city,country||rn rchild,country||(rn-1) rfather
2 FROM
3 (SELECT test.country ,test.city,row_number() over (PARTITION BY test.country ORDER BY test.city) rn
4 FROM test)
5 /
日本大阪日本1 日本0
日本东京日本2 日本1
中国上海中国1 中国0
中国台北中国2 中国1
中国香港中国3 中国2
step 3 利用sys_connect_by_path生成结果集
SELECT country,sys_connect_by_path(city,',') city
FROM
(SELECT country,city,country||rn rchild,country||(rn-1) rfather
FROM
(SELECT test.country ,test.city,row_number() over (PARTITION BY test.country ORDER BY test.city) rn FROM test)) CONNECT BY PRIOR rchild=rfather START WITH rfather LIKE '%0'
日本 ,大阪
日本 ,大阪,东京
中国 ,上海
中国 ,上海,台北
中国 ,上海,台北,香港
step 4 最终步骤，筛选结果集合
SQL> SELECT country,max(substr(city,2)) city
2 FROM
3 (SELECT country,sys_connect_by_path(city,',') city
4 FROM
5 (SELECT country,city,country||rn rchild,country||(rn-1) rfather
6 FROM
7 (SELECT test.country ,test.city,row_number() over (PARTITION BY test.country ORDER BY test.city) rn
8 FROM test))
9 CONNECT BY PRIOR rchild=rfather START WITH rfather LIKE '%0')
10 GROUP BY country;

COUNTRY CITY
-------------------- -------
中国上海,台北,香港
日本大阪,东京