<think>
我们正在讨论Oracle 10g中分组后字符串连接的方法。需要注意的是，用户明确提到是在Oracle 10g数据库中，而引用中提到的LISTAGG函数是在Oracle 11g中引入的[^1][^2][^5]，因此不能使用LISTAGG。
在Oracle 10g中，我们可以使用以下几种方法实现分组字符串连接：
1. 使用WM_CONCAT函数（非官方，但在10g中常见）
2. 使用SYS_CONNECT_BY_PATH函数（通过层次查询实现）
3. 使用自定义聚合函数
由于用户要求分组后字符串连接，这里我们重点介绍前两种方法，因为自定义聚合函数相对复杂。
方法一：WM_CONCAT函数（如果可用）
注意：WM_CONCAT是Oracle内部函数，在10g中通常存在，但未公开文档，在11g及以上版本中可能被移除或需要额外操作启用。
示例：
SELECT deptno, WM_CONCAT(ename) AS enames
FROM emp
GROUP BY deptno;
方法二：使用SYS_CONNECT_BY_PATH（适用于10g且稳定）
步骤：
a. 使用ROW_NUMBER()为每个分组内的行编号
b. 使用CONNECT BY在同一分组内构建层次结构（以行号作为父子关系）
c. 使用SYS_CONNECT_BY_PATH连接字符串，并取最大路径（即根节点到叶子节点的完整路径）
示例代码：
WITH temp AS (
SELECT deptno, ename,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS rn,
COUNT(*) OVER (PARTITION BY deptno) AS cnt
FROM emp
)
SELECT deptno,
LTRIM(MAX(SYS_CONNECT_BY_PATH(ename, ',')) KEEP (DENSE_RANK LAST ORDER BY curr), ',') AS enames
FROM (
SELECT deptno, ename, rn, cnt,
CONNECT_BY_ROOT rn AS root_rn -- 用于分组
FROM temp
CONNECT BY PRIOR rn = rn - 1 AND PRIOR deptno = deptno
)
WHERE rn = cnt -- 取每个分组最后一行的路径（即完整路径）
GROUP BY deptno;
方法三：使用XMLAGG（10g支持，但需要处理XML标签）
示例：
SELECT deptno,
RTRIM(XMLAGG(XMLELEMENT(e, ename || ',')).EXTRACT('//text()'), ',') AS enames
FROM emp
GROUP BY deptno;
注意：XMLAGG可能会改变原始排序，如果需要排序，可以在XMLAGG内使用ORDER BY子句。
由于用户环境是10g，推荐方法二（SYS_CONNECT_BY_PATH）或方法一（WM_CONCAT）如果可用。但WM_CONCAT在10g中默认安装，只是未公开，所以如果可用则更简单。
下面给出一个更简洁的SYS_CONNECT_BY_PATH写法（参考自常见写法）：
SELECT deptno,
MAX(LTRIM(SYS_CONNECT_BY_PATH(ename, ','), ',')) AS enames
FROM (
SELECT deptno, ename,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS rn
FROM emp
)
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1 AND PRIOR deptno = deptno
GROUP BY deptno;
这个查询的逻辑：
1. 子查询为每个部门内的员工按姓名排序并编号（rn）
2. 从rn=1开始连接，连接条件：同一个部门（deptno相同）且前一个rn等于当前rn-1
3. 这样每个部门都会形成一棵树，从rn=1开始，一直连接到最后一个rn
4. 每个部门的最大路径（即最后一行）就是完整的连接字符串
5. 使用LTRIM去除第一个字符串前的分隔符（逗号）
注意：如果分组内的数据量很大，这种方法可能会导致性能问题，因为它需要构建层次结构。
综上所述，针对Oracle 10g，我们可以根据实际情况选择：
1. 如果WM_CONCAT可用且不担心未来兼容性，使用WM_CONCAT。
2. 否则，使用SYS_CONNECT_BY_PATH方法。
3. 或者使用XMLAGG（注意处理XML标签和排序）。
下面是一个使用WM_CONCAT的完整示例（如果可用）：
SELECT department_id, WM_CONCAT(last_name) AS employees
FROM employees
GROUP BY department_id;
使用SYS_CONNECT_BY_PATH的完整示例（通用）：
SELECT department_id,
MAX(LTRIM(SYS_CONNECT_BY_PATH(last_name, ','), ',')) AS employees
FROM (
SELECT department_id, last_name,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY last_name) AS rn
FROM employees
)
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1 AND PRIOR department_id = department_id
GROUP BY department_id;
使用XMLAGG的示例：
SELECT department_id,
RTRIM(XMLAGG(XMLELEMENT(e, last_name || ',') ORDER BY last_name).EXTRACT('//text()'), ',') AS employees
FROM employees
GROUP BY department_id;
注意：在XMLAGG中，我们使用ORDER BY来保证排序，然后使用RTRIM去除末尾多余的逗号。
最后提醒：以上方法在10g中可用，但11g及以上建议使用LISTAGG[^5]。
</think>
### Oracle 10g 分组后字符串连接的实现方法
在 Oracle 10g 中实现分组字符串连接（因为 `LISTAGG` 是 11g 引入的[^1][^5]），主要有以下几种方法：
---
#### 方法一：WM_CONCAT 函数（未公开但常用）
```sql
SELECT department_id,
WM_CONCAT(last_name) AS employees
FROM employees
GROUP BY department_id;
```
**特点**：
- 10g 中普遍存在但未官方文档化
- 结果自动用逗号连接
- 默认无排序控制
---
#### 方法二：SYS_CONNECT_BY_PATH（层级查询法）
```sql
SELECT department_id,
MAX(LTRIM(SYS_CONNECT_BY_PATH(last_name, ','), ',')) AS employees
FROM (
SELECT department_id,
last_name,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY last_name) AS rn
FROM employees
)
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1 AND PRIOR department_id = department_id
GROUP BY department_id;
```
**步骤分解**：
1. 子查询为每个分组生成序号：
```sql
SELECT department_id, last_name,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY last_name) rn
FROM employees
```
2. 构建递归路径：
```sql
START WITH rn = 1 -- 从每组第一条开始
CONNECT BY PRIOR rn = rn - 1 -- 连接下一条记录
AND PRIOR department_id = department_id -- 同组内连接
```
3. 去除前缀逗号：
```sql
LTRIM(SYS_CONNECT_BY_PATH(...), ',')
```
---
#### 方法三：XMLAGG 函数（XML处理法）
```sql
SELECT department_id,
RTRIM(XMLAGG(XMLELEMENT(e, last_name || ',')).EXTRACT('//text()'), ',') AS employees
FROM employees
GROUP BY department_id;
```
**原理**：
1. `XMLELEMENT(e, last_name || ',')`：创建带逗号的XML元素
2. `XMLAGG()`：聚合XML元素
3. `EXTRACT('//text()')`：提取文本内容
4. `RTRIM(..., ',')`：移除末尾多余逗号
---
#### 性能对比表
| 方法 | 排序控制 | 10g兼容性 | 官方支持 | 大数组性能 |
|-------------------|----------|-----------|----------|------------|
| WM_CONCAT | ❌ | ✔️ | ❌ | ⭐⭐ |
| SYS_CONNECT_BY_PATH| ✔️ | ✔️ | ✔️ | ⭐ |
| XMLAGG | ❌ | ✔️ | ✔️ | ⭐⭐ |
> **特别提示**：若升级到 Oracle 11g+，强烈建议使用标准函数 `LISTAGG`[^5]：
> ```sql
> SELECT department_id,
> LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name)
> FROM employees
> GROUP BY department_id;
> ```
---
#### 自定义排序的 SYS_CONNECT_BY_PATH 示例
按雇佣日期排序：
```sql
SELECT department_id,
MAX(LTRIM(SYS_CONNECT_BY_PATH(last_name, ';'), ';')) AS employees
FROM (
SELECT department_id,
last_name,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date) AS rn
FROM employees
)
CONNECT BY PRIOR rn = rn - 1
AND PRIOR department_id = department_id
START WITH rn = 1
GROUP BY department_id;
```
本文介绍如何利用Oracle提供的LISTAGG函数来实现排序集合功能，通过示例展示如何查询Products表中每个类别的产品名称，并按照产品名称进行排序。
扫一扫
422
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
