oracle实现将字段按逗号拼接/按逗号分为多行_oracle根据逗号一行变多行

一、拼接

1.普通拼接

使用 LISTAGG 函数来实现：

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

这段代码会将表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;
1
2
3
4
5
6
7
8
9
10
11

这段代码会在表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;
1
2
3
4
5

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

4.函数说明

LISTAGG

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

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

例如，在上面的示例代码中，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
1
2
3
4
5
6
7

该代码实现将字段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;
1
2
3
4
5

如果运行上面那段代码后出现了无限循环重复的记录，可能是因为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;
1
2
3
4
5
6

在上面这段代码中，我们添加了一个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：可选参数，指定返回哪个子表达式。
1
2
3
4
5
6
7
8

例如，在上面的示例代码中，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;
1
2
3

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

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

SELECT employee_id, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
1
2
3
4

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

4.普通分行方法2

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

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

这段代码使用了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, ',', '","') || '"'))
1
2
3

这段代码首先使用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
1
2
3
4

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

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

查询B：

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

尽管这两个查询都比原始查询好得多，但在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;
1
2
3
4