一、拼接

1.普通拼接

使用 LISTAGG 函數(shù)來實(shí)現(xiàn)：

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

這段代碼會(huì)將表A中每個(gè)B對(duì)應(yīng)的多個(gè)C值用逗號(hào)拼接起來，形成一個(gè)新的C列。

2.進(jìn)階：限制拼接個(gè)數(shù)并去重

如果限制拼接的字段個(gè)數(shù)及去重可以按如下代碼：

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;

這段代碼會(huì)在表A中選擇B和C兩個(gè)字段，并使用 DISTINCT 關(guān)鍵字去除重復(fù)的C值。然后使用 ROW_NUMBER 函數(shù)為每個(gè)B對(duì)應(yīng)的C值進(jìn)行編號(hào)，最后使用 LISTAGG 函數(shù)將每個(gè)B對(duì)應(yīng)的前100個(gè)C值用逗號(hào)拼接起來，形成一個(gè)新的C列。

3.進(jìn)階：拼接除去當(dāng)前值的其他值

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;

這段代碼會(huì)使用LISTAGG函數(shù)將表T中同一個(gè)A下的除了當(dāng)前的B其他值按逗號(hào)拼接為一個(gè)字符串，并作為C字段的值。

4.函數(shù)說明 LISTAGG

LISTAGG是Oracle中的一個(gè)聚合函數(shù)，用于將多行數(shù)據(jù)連接成一個(gè)字符串。它的語法為：

LISTAGG(measure_expression [, 'delimiter']) WITHIN GROUP (ORDER BY sort_expression)
measure_expression：要連接的表達(dá)式。
delimiter：可選參數(shù)，指定連接時(shí)使用的分隔符。默認(rèn)值為空字符串。
sort_expression：指定連接順序的表達(dá)式。

例如，在上面的示例代碼中，LISTAGG(B, ‘,’) WITHIN GROUP (ORDER BY B)表示將B字段的值按照逗號(hào)分隔符連接起來，并按照B字段的值排序。

需要注意的是，LISTAGG函數(shù)只能在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

該代碼實(shí)現(xiàn)將字段B根據(jù)逗號(hào)分為多行

2.進(jìn)階：去重及控制分行行數(shù)

如果運(yùn)行上面那段代碼后出現(xiàn)了多條重復(fù)的記錄，可能是因?yàn)楸鞹中存在多條A字段值相同的記錄。由于我們?cè)诖a中使用了PRIOR A = A這個(gè)條件來控制生成新行，所以如果表T中存在多條A字段值相同的記錄，那么每條記錄都會(huì)被分割成多行，從而導(dǎo)致結(jié)果中出現(xiàn)重復(fù)的記錄。

為了避免這種情況，你可以在查詢中添加一個(gè)DISTINCT關(guān)鍵字來去除重復(fù)的記錄，例如

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;

如果運(yùn)行上面那段代碼后出現(xiàn)了無限循環(huán)重復(fù)的記錄，可能是因?yàn)镃ONNECT BY子句中的條件沒有正確設(shè)置。在使用CONNECT BY子句生成多行結(jié)果時(shí)，需要確保遞歸條件能夠在某個(gè)時(shí)刻不再滿足，從而終止遞歸。

在上面那段代碼中，我們使用了REGEXP_SUBSTR(B, ‘[^,]+’, 1, LEVEL) IS NOT NULL作為遞歸條件。這個(gè)條件表示只要REGEXP_SUBSTR函數(shù)返回的結(jié)果不為空，就繼續(xù)生成下一行。如果表T中的B字段包含無限個(gè)逗號(hào)分隔的子串，那么這個(gè)條件將永遠(yuǎn)成立，從而導(dǎo)致無限循環(huán)重復(fù)的記錄。

為了避免這種情況，你可以在查詢中添加一個(gè)限制條件來控制遞歸的深度，例如：

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;

在上面這段代碼中，我們添加了一個(gè)WHERE LEVEL <= 10條件來控制遞歸的深度。這樣，在使用CONNECT BY子句生成多行結(jié)果時(shí)，只會(huì)生成最多10行結(jié)果，從而避免了無限循環(huán)重復(fù)的記錄。

3.函數(shù)說明

PRIOR

PRIOR是Oracle中用于層次查詢的關(guān)鍵字。它用于在CONNECT BY子句中指定父行和子行之間的關(guān)系。例如，CONNECT BY PRIOR employee_id = manager_id表示父行的employee_id字段等于子行的manager_id字段。

在上面的示例代碼中，PRIOR C = C表示父行和子行的C字段值相同，這樣可以確保每個(gè)B字段值都與其對(duì)應(yīng)的C字段值關(guān)聯(lián)。而PRIOR SYS_GUID() IS NOT NULL是一個(gè)技巧，用于防止循環(huán)遞歸。

REGEXP_SUBSTR

REGEXP_SUBSTR是Oracle中的一個(gè)正則表達(dá)式函數(shù)，用于從字符串中提取匹配正則表達(dá)式的子字符串。它的語法為：

REGEXP_SUBSTR(source_string, 
pattern [, position [, occurrence [, match_parameter [, sub_expression]]]])
source_string：要搜索的字符串。
pattern：正則表達(dá)式模式。
position：可選參數(shù)，指定開始搜索的位置。默認(rèn)值為1。
occurrence：可選參數(shù)，指定返回第幾個(gè)匹配項(xiàng)。默認(rèn)值為1。
match_parameter：可選參數(shù)，指定匹配行為。例如，'i'表示不區(qū)分大小寫。
sub_expression：可選參數(shù)，指定返回哪個(gè)子表達(dá)式。

例如，在上面的示例代碼中，REGEXP_SUBSTR(B, ‘[^,]+’, 1, LEVEL)表示從B字段的第1個(gè)字符開始搜索，返回第LEVEL個(gè)匹配項(xiàng)，其中正則表達(dá)式模式為[^,]+，表示匹配一個(gè)或多個(gè)非逗號(hào)字符。

CONNECT BY

CONNECT BY是Oracle中用于層次查詢的子句。它用于指定父行和子行之間的關(guān)系，以便從表中檢索層次結(jié)構(gòu)數(shù)據(jù)。

例如，假設(shè)你有一個(gè)員工表，其中包含員工ID和經(jīng)理ID兩個(gè)字段。你可以使用以下查詢來檢索員工的層次結(jié)構(gòu)：

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

這個(gè)查詢會(huì)返回一個(gè)結(jié)果集，其中每一行都表示一個(gè)員工和其直接經(jīng)理之間的關(guān)系。

你可以使用START WITH子句來指定層次結(jié)構(gòu)的起始點(diǎn)，例如：

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

這個(gè)查詢會(huì)返回一個(gè)結(jié)果集，其中只包含那些沒有經(jīng)理（即頂級(jí)經(jīng)理）的員工及其下屬。

4.普通分行方法2

如果你不想使用遞歸來實(shí)現(xiàn)將字段分為多行，你可以使用Oracle的XMLTABLE函數(shù)來實(shí)現(xiàn)。例如：

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

這段代碼使用了REPLACE函數(shù)來將B字段中的逗號(hào)替換為","，然后使用XMLTABLE函數(shù)來將替換后的字符串轉(zhuǎn)換為多行結(jié)果。

例子：

假設(shè)我們有一個(gè)表T，其中包含兩個(gè)字段A和B，如下所示：

A B1 a,b,c2 d,e,f我們希望將B字段根據(jù)逗號(hào)分為多行，得到如下結(jié)果：

A B1 a1 b1 c2 d2 e2 f為了實(shí)現(xiàn)這個(gè)需求，我們可以使用以下SQL語句：

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

這段代碼首先使用REPLACE函數(shù)將B字段中的逗號(hào)替換為",“，例如將a,b,c替換為a”,“b”,“c。然后在替換后的字符串前后分別添加一個(gè)雙引號(hào)，得到"a”,“b”,“c”。

接下來，使用XMLTABLE函數(shù)將替換后的字符串轉(zhuǎn)換為多行結(jié)果。XMLTABLE函數(shù)會(huì)將輸入的字符串解析為一個(gè)XML文檔，并根據(jù)指定的XPath表達(dá)式來提取數(shù)據(jù)。在這個(gè)例子中，我們使用了默認(rèn)的XPath表達(dá)式，即/ROWSET/ROW，來提取所有行。

最后，使用COLUMN_VALUE關(guān)鍵字來選擇每一行的值，并將其作為B字段的值返回。

三、優(yōu)化：CONNECT BY與WHERE

在Oracle中，CONNECT BY子句在應(yīng)用WHERE條件之前應(yīng)用。因此，WHERE約束不會(huì)幫助優(yōu)化CONNECT BY。

例如，下面的查詢可能會(huì)執(zhí)行全表掃描（忽略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

盡管這兩個(gè)查詢都比原始查詢好得多，但在Oracle 10g Release 2上，查詢B的性能比A好得多。

注：START WITH子句是可選的，它指定了層次查詢的根行。如果省略此子句，則Oracle將表中的所有行用作根行。START WITH條件可以包含子查詢，但不能包含標(biāo)量子查詢表達(dá)式1。

例如，下面的查詢將從employee_id = 100的行開始，并返回層次結(jié)構(gòu)中該行下的所有行：

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

總結(jié) 

到此這篇關(guān)于oracle實(shí)現(xiàn)將字段按逗號(hào)拼接/按逗號(hào)分為多行的文章就介紹到這了,更多相關(guān)oracle將字段按逗號(hào)拼接內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論