我有這樣的表格：
| id | 全名 |
|---|---|
| 1 | |
| 約翰-史密斯 | |
| Jim Jonson |
我想得到這樣的結果：
| id | 全名 |
|---|---|
| 1 | |
| 約翰-史密斯 |
因此，我需要SELECT DISTINCT full_name FROM table，以便
John Smith和Smith John是同一個人，也Jim Jonson和JimJonson。
我希望我解釋得很好。你能幫助我嗎？
uj5u.com熱心網友回復：
一步一步來。閱讀代碼中的注釋。
SQL> with test（id, full_name） as
2 --樣本資料。
3 （select 1, 'John Smith' from dual union all.
4 select 2, 'Smith John' from dual union all.
5 select 3, 'Jim Jonson' from dual union all >。
6 select 4, 'JimJonson' from dual
7 ）。)
8 temp as
9 -- 分割全名到行
10 (select id,
11 regexp_substr(full_name, ' [^ ] ', 1, column_value) val,
12 column_value cv
13 from test cross join
14 table(cast(multiset(select level from double
15 connect by level < = regexp_count(full_name, ' ') 1
16 ) as sys.odcinumberlist))
17 ）。)
18 temp2as
19 --聚合全名，"單詞 "之間沒有空格。
20 (select id,
21 listagg（val, '') within group （order by val） full_name
22 from temp
23 group by id
24 ）。)
25 temp3 as
26 --只獲取不同的值。
27 (select min(b.id) id。
28 b.full_name
29 from temp2 b
30 group by b.full_name
31 )
32 --最后，加入TEMP3和樣本資料。
33 select b.id。
34 a.full_name
35 from test a join temp3 b on b.id= a.id
36 order by a.id。
ID全稱是FULL_NAME
---------- ----------------------------------------
1 John Smith
3 Jim Jonson
SQL>/span>
uj5u.com熱心網友回復：
你可以通過姓名的首字母大寫字母以無支點的方式拆分full_name值，并按字母排序，通過使用LISTAGG()函式進行組合，并在最后一步應用MIN()聚合，例如
WITH t(id,ful_name) AS
(
SELECT 1, 'John Smith' FROM dual UNION ALL
SELECT 2, 'Smith John' FROM dual UNION ALL
SELECT 3, 'Jim Jonson' FROM dual UNION ALL
SELECT 4, 'JimJonson' FROM dual
), t2 AS
(
SELECT id,
TRIM(SUBSTR(full_name,column_value,LEAD(column_value,1, LENGTH(full_name) OVER (PARTITION BY id ORDER BY id, column_value)-1)) AS names
FROM t,
TABLE(CAST(MULTISET(SELECT REGEXP_INSTR(ful_name, '[A-Z] ',1, level)
FROM double
CONNECT BY level <= REGEXP_COUNT(full_name,' [A-Z') AS sys.odcivarchar2list ))
), t3AS
(
SELECT id, LISTAGG（names, ' ') WITHIN GROUP (ORDER BY ID, names） AS full_name
FROM t2
GROUP BY id
)
SELECT MIN(id) AS min_id, full_name
FROM t3
GROUP BY full_name
ORDER BY min_id
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/332202.html
標籤：