一、拼接

1.普通拼接

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

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

這段代碼會將表A中每個B對應的多個C值用逗號拼接起來，形成一個新的C列。

2.進階：限制拼接個數(shù)并去重

如果限制拼接的字段個數(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;

這段代碼會在表A中選擇B和C兩個字段，并使用 DISTINCT 關鍵字去除重復的C值。然后使用 ROW_NUMBER 函數(shù)為每個B對應的C值進行編號，最后使用 LISTAGG 函數(shù)將每個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函數(shù)將表T中同一個A下的除了當前的B其他值按逗號拼接為一個字符串，并作為C字段的值。

4.函數(shù)說明 LISTAGG

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

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

例如，在上面的示例代碼中，LISTAGG(B, ‘,’) WITHIN GROUP (ORDER BY B)表示將B字段的值按照逗號分隔符連接起來，并按照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

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

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

如果運行上面那段代碼后出現(xiàn)了多條重復的記錄，可能是因為表T中存在多條A字段值相同的記錄。由于我們在代碼中使用了PRIOR A = A這個條件來控制生成新行，所以如果表T中存在多條A字段值相同的記錄，那么每條記錄都會被分割成多行，從而導致結果中出現(xiàn)重復的記錄。

為了避免這種情況，你可以在查詢中添加一個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;

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

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

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

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行結果，從而避免了無限循環(huán)重復的記錄。

3.函數(shù)說明

PRIOR

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

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

REGEXP_SUBSTR

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

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

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

CONNECT BY

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

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

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

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

你可以使用START WITH子句來指定層次結構的起始點，例如：

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

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

4.普通分行方法2

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

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

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

例子：

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

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

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

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

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

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

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

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

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

例如，下面的查詢可能會執(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

盡管這兩個查詢都比原始查詢好得多，但在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;

總結 

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

最新評論