我有一張桌子：
| ID | 文本 |
|---|---|
| 1 | A1 文本 - 單詞 |
| 2 | A1 文本 - 單詞a |
| 3 | B1 句子 - 句子 |
| 4 | B1 句子 - 句子 b |
我想要達到的目標：
| ID | 文本 |
|---|---|
| 1 | A1 文本 - 單詞，A1 文本 - 單詞a |
| 2 | B1 句子 - 句子，B1 句子 - 句子 b |
我不確定如何解決這個問題，或者是否有可以處理這個問題的函式？我至少需要一個建議才能開始。
編輯 - 我稍微更新了表格。當然，我不想將整個表格匯總為 1 行，希望表格能更清楚地顯示我想說的內容。
uj5u.com熱心網友回復：
選項1：
如果要查找字串開頭與下一個字串匹配的連續行的鏈，則可以使用分層函式并使用以下方法整理行SYS_CONNECT_BY_PATH：
SELECT CONNECT_BY_ROOT id AS id,
SUBSTR(
SYS_CONNECT_BY_PATH(Text, ', '),
3
) AS Text
FROM table_name
WHERE CONNECT_BY_ISLEAF = 1
START WITH
id IN (SELECT id
FROM table_name
WHERE CONNECT_BY_ISLEAF = 1
AND LEVEL = 1
CONNECT BY NOCYCLE
PRIOR text LIKE text || '%'
)
CONNECT BY NOCYCLE
text LIKE PRIOR text || '%';
其中，對于樣本資料：
CREATE TABLE table_name (ID, Text) AS
SELECT 1, 'A1 Text - words' FROM DUAL UNION ALL
SELECT 2, 'A1 Text - wordsa' FROM DUAL UNION ALL
SELECT 3, 'B1 Sentence - sentence' FROM DUAL UNION ALL
SELECT 4, 'B1 Sentence - sentence b' FROM DUAL;
輸出：
ID 文本 1 A1 文本 - 單詞，A1 文本 - 單詞a 3 B1 句子 - 句子，B1 句子 - 句子 b
選項 2：
從 Oracle 12 開始，如果要查找第一個字串，然后查找所有以該第一個字串開頭的字串，則可以使用MATCH_RECOGNIZE：
SELECT MIN(first_id) AS id,
LISTAGG(text, ',') WITHIN GROUP (ORDER BY text) AS text
FROM table_name
MATCH_RECOGNIZE(
ORDER BY text
MEASURES
FIRST(id) AS first_id,
MATCH_NUMBER() AS mno
ALL ROWS PER MATCH
PATTERN (matches )
DEFINE matches AS (text LIKE FIRST(text) || '%')
)
GROUP BY mno
Which, for the sample data, gives the same output (but may give a different output if there was more data as Option 1 is finding individual paths through the hierarchy and Option 2 is finding all children that match an ancestor).
In earlier versions, you should get the equivalent result using:
SELECT root_id AS id,
LISTAGG(text, ', ') WITHIN GROUP (ORDER BY ROWNUM) AS text
FROM (
SELECT DISTINCT
CONNECT_BY_ROOT id AS root_id,
id,
text
FROM table_name
START WITH
id IN (SELECT id
FROM table_name
WHERE CONNECT_BY_ISLEAF = 1
AND LEVEL = 1
CONNECT BY NOCYCLE
PRIOR text LIKE text || '%'
)
CONNECT BY NOCYCLE
text LIKE PRIOR text || '%'
ORDER SIBLINGS BY text
)
GROUP BY root_id
db<>fiddle here
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/454342.html
上一篇：如何使用左連接從多個表中獲取值？