oracle实现将字段按逗号拼接/按逗号分为多行

叁歲伎倆 2024-03-23 15:15 85阅读 0赞

#### oracle实现将字段按逗号拼接/按逗号分行 ####

*  一、拼接
 *   *  1.普通拼接
     *  2.进阶：限制拼接个数并去重
     *  3.进阶：拼接除去当前值的其他值
     *  4.函数说明
     *   *  LISTAGG
 *  二、分行
 *   *  1.普通分行方法1
     *  2.进阶：去重及控制分行行数
     *  3.函数说明
     *   *  PRIOR
         *  REGEXP\_SUBSTR
         *  CONNECT BY
     *  4.普通分行方法2
 *  三、优化：CONNECT BY与WHERE

## 一、拼接 ##

### 1.普通拼接 ###

使用 LISTAGG 函数来实现：

SELECT B, LISTAGG(C, ',') WITHIN GROUP (ORDER BY C) AS C
    FROM A
    GROUP BY B;

这段代码会将表A中每个B对应的多个C值用逗号拼接起来，形成一个新的C列。

### 2.进阶：限制拼接个数并去重 ###

如果限制拼接的字段个数及去重可以按如下代码：

SELECT B, LISTAGG(C, ',') WITHIN GROUP (ORDER BY C) AS C
    FROM (
      SELECT B, C,
        ROW_NUMBER() OVER (PARTITION BY B ORDER BY C) AS rn
      FROM (
        SELECT DISTINCT B, C
        FROM A
      )
    )
    WHERE rn <= 100
    GROUP BY B;

这段代码会在表A中选择B和C两个字段，并使用 DISTINCT 关键字去除重复的C值。然后使用 ROW\_NUMBER 函数为每个B对应的C值进行编号，最后使用 LISTAGG 函数将每个B对应的前100个C值用逗号拼接起来，形成一个新的C列。

### 3.进阶：拼接除去当前值的其他值 ###

SELECT t1.A, t1.B,
           (SELECT LISTAGG(t2.B, ',') WITHIN GROUP (ORDER BY t2.B)
            FROM T t2
            WHERE t2.A = t1.A AND t2.B != t1.B) AS C
    FROM T t1;

这段代码会使用LISTAGG函数将表T中同一个A下的除了当前的B其他值按逗号拼接为一个字符串，并作为C字段的值。

### 4.函数说明 ###

#### LISTAGG ####

LISTAGG是Oracle中的一个聚合函数，用于将多行数据连接成一个字符串。它的语法为：

LISTAGG(measure_expression [, 'delimiter']) WITHIN GROUP (ORDER BY sort_expression)
    measure_expression：要连接的表达式。
    delimiter：可选参数，指定连接时使用的分隔符。默认值为空字符串。
    sort_expression：指定连接顺序的表达式。

例如，在上面的示例代码中，LISTAGG(B, ‘,’) WITHIN GROUP (ORDER BY B)表示将B字段的值按照逗号分隔符连接起来，并按照B字段的值排序。

需要注意的是，LISTAGG函数只能在SELECT语句的GROUP BY子句中使用。

## 二、分行 ##

### 1.普通分行方法1 ###

SELECT
    	A,
    	REGEXP_SUBSTR(B,'[^,]+',1,LEVEL)  B
    FROM T1
    CONNECT BY REGEXP_SUBSTR(B,'[^,]+',1,LEVEL) IS NOT NULL
    AND PRIOD A = A
    AND PRIOD SYS_GUID

该代码实现将字段B根据逗号分为多行

### 2.进阶：去重及控制分行行数 ###

如果运行上面那段代码后出现了多条重复的记录，可能是因为表T中存在多条A字段值相同的记录。由于我们在代码中使用了PRIOR A = A这个条件来控制生成新行，所以如果表T中存在多条A字段值相同的记录，那么每条记录都会被分割成多行，从而导致结果中出现重复的记录。

为了避免这种情况，你可以在查询中添加一个DISTINCT关键字来去除重复的记录，例如

SELECT DISTINCT A, REGEXP_SUBSTR(B, '[^,]+', 1, LEVEL) AS B
    FROM T
    CONNECT BY REGEXP_SUBSTR(B, '[^,]+', 1, LEVEL) IS NOT NULL
    AND PRIOR A = A
    AND PRIOR SYS_GUID() IS NOT NULL;

如果运行上面那段代码后出现了无限循环重复的记录，可能是因为CONNECT BY子句中的条件没有正确设置。在使用CONNECT BY子句生成多行结果时，需要确保递归条件能够在某个时刻不再满足，从而终止递归。

在上面那段代码中，我们使用了REGEXP\_SUBSTR(B, ‘\[^,\]+’, 1, LEVEL) IS NOT NULL作为递归条件。这个条件表示只要REGEXP\_SUBSTR函数返回的结果不为空，就继续生成下一行。如果表T中的B字段包含无限个逗号分隔的子串，那么这个条件将永远成立，从而导致无限循环重复的记录。

为了避免这种情况，你可以在查询中添加一个限制条件来控制递归的深度，例如：

SELECT A, REGEXP_SUBSTR(B, '[^,]+', 1, LEVEL) AS B
    FROM T
    WHERE LEVEL <= 10 -- 控制递归深度
    CONNECT BY REGEXP_SUBSTR(B, '[^,]+', 1, LEVEL) IS NOT NULL
    AND PRIOR A = A
    AND PRIOR SYS_GUID() IS NOT NULL;

在上面这段代码中，我们添加了一个WHERE LEVEL <= 10条件来控制递归的深度。这样，在使用CONNECT BY子句生成多行结果时，只会生成最多10行结果，从而避免了无限循环重复的记录。

### 3.函数说明 ###

#### PRIOR ####

PRIOR是Oracle中用于层次查询的关键字。它用于在CONNECT BY子句中指定父行和子行之间的关系。例如，CONNECT BY PRIOR employee\_id = manager\_id表示父行的employee\_id字段等于子行的manager\_id字段。

在上面的示例代码中，PRIOR C = C表示父行和子行的C字段值相同，这样可以确保每个B字段值都与其对应的C字段值关联。而PRIOR SYS\_GUID() IS NOT NULL是一个技巧，用于防止循环递归。

#### REGEXP\_SUBSTR ####

REGEXP\_SUBSTR是Oracle中的一个正则表达式函数，用于从字符串中提取匹配正则表达式的子字符串。它的语法为：

REGEXP_SUBSTR(source_string, 
    pattern [, position [, occurrence [, match_parameter [, sub_expression]]]])
    source_string：要搜索的字符串。
    pattern：正则表达式模式。
    position：可选参数，指定开始搜索的位置。默认值为1。
    occurrence：可选参数，指定返回第几个匹配项。默认值为1。
    match_parameter：可选参数，指定匹配行为。例如，'i'表示不区分大小写。
    sub_expression：可选参数，指定返回哪个子表达式。

例如，在上面的示例代码中，REGEXP\_SUBSTR(B, ‘\[^,\]+’, 1, LEVEL)表示从B字段的第1个字符开始搜索，返回第LEVEL个匹配项，其中正则表达式模式为\[^,\]+，表示匹配一个或多个非逗号字符。

#### CONNECT BY ####

CONNECT BY是Oracle中用于层次查询的子句。它用于指定父行和子行之间的关系，以便从表中检索层次结构数据。

例如，假设你有一个员工表，其中包含员工ID和经理ID两个字段。你可以使用以下查询来检索员工的层次结构：

SELECT employee_id, manager_id
    FROM employees
    CONNECT BY PRIOR employee_id = manager_id;

这个查询会返回一个结果集，其中每一行都表示一个员工和其直接经理之间的关系。

你可以使用START WITH子句来指定层次结构的起始点，例如：

SELECT employee_id, manager_id
    FROM employees
    START WITH manager_id IS NULL
    CONNECT BY PRIOR employee_id = manager_id;

这个查询会返回一个结果集，其中只包含那些没有经理（即顶级经理）的员工及其下属。

### 4.普通分行方法2 ###

如果你不想使用递归来实现将字段分为多行，你可以使用Oracle的XMLTABLE函数来实现。例如：

SELECT A, COLUMN_VALUE AS B
    FROM T,
         XMLTABLE(('"' || REPLACE(B, ',', '","') || '"'))

这段代码使用了REPLACE函数来将B字段中的逗号替换为","，然后使用XMLTABLE函数来将替换后的字符串转换为多行结果。

例子：

假设我们有一个表T，其中包含两个字段A和B，如下所示：

A B  
1 a,b,c  
2 d,e,f  
我们希望将B字段根据逗号分为多行，得到如下结果：

A B  
1 a  
1 b  
1 c  
2 d  
2 e  
2 f  
为了实现这个需求，我们可以使用以下SQL语句：

SELECT A, COLUMN_VALUE AS B
    FROM T,
         XMLTABLE(('"' || REPLACE(B, ',', '","') || '"'))

这段代码首先使用REPLACE函数将B字段中的逗号替换为",“，例如将a,b,c替换为a”,“b”,“c。然后在替换后的字符串前后分别添加一个双引号，得到"a”,“b”,“c”。

接下来，使用XMLTABLE函数将替换后的字符串转换为多行结果。XMLTABLE函数会将输入的字符串解析为一个XML文档，并根据指定的XPath表达式来提取数据。在这个例子中，我们使用了默认的XPath表达式，即/ROWSET/ROW，来提取所有行。

最后，使用COLUMN\_VALUE关键字来选择每一行的值，并将其作为B字段的值返回。

## 三、优化：CONNECT BY与WHERE ##

在Oracle中，CONNECT BY子句在应用WHERE条件之前应用。因此，WHERE约束不会帮助优化CONNECT BY。

例如，下面的查询可能会执行全表扫描（忽略dept\_id的选择性）：

SELECT * FROM employees
    WHERE dept_id = 'SALE'
    START WITH manager_id is null
    CONNECT BY PRIOR employee_id = manager_id

有两种方法可以提高性能： 查询A：

SELECT * FROM employees
    START WITH manager_id is null AND dept_id = 'SALE'
    CONNECT BY PRIOR employee_id = manager_id

查询B：

SELECT * FROM (
        SELECT * FROM employees WHERE dept_id = 'SALE'
    )
    START WITH manager_id is null
    CONNECT BY PRIOR employee_id = manager_id

尽管这两个查询都比原始查询好得多，但在Oracle 10g Release 2上，查询B的性能比A好得多。

注：START WITH子句是可选的，它指定了层次查询的根行。如果省略此子句，则Oracle将表中的所有行用作根行。START WITH条件可以包含子查询，但不能包含标量子查询表达式1。

例如，下面的查询将从employee\_id = 100的行开始，并返回层次结构中该行下的所有行：

SELECT employee_id, last_name, manager_id
    FROM employees
    START WITH employee_id = 100
    CONNECT BY PRIOR employee_id = manager_id;