我有一個帶有逗號分隔值的技能列的表。每個學生的技能可以重復。我需要使用 group by 列出每個學生的獨特技能。
我應該使用什么才能獲得沒有重復的串列。請幫忙。
輸入表 student_skills：
| 學生卡 | 學期 | 技能 |
|---|---|---|
| 101 | 1 | 三、SQL |
| 101 | 2 | C、CPP |
| 102 | 1 | CPP, 爪哇 |
| 102 | 2 | 爪哇，JavaScript |
期望的結果：
| 學生卡 | 技能 |
|---|---|
| 101 | C、SQL、CPP |
| 102 | CPP、Java、JavaScript |
SQL查詢創建表，插入資料：
create table student_skills
(STUDENT_ID number(10),
SEMESTER varchar2(5),
SKILLS varchar2(50));
insert into student_skills (STUDENT_ID, SEMESTER, SKILLS)
values (101, '1', 'C, SQL');
insert into student_skills (STUDENT_ID, SEMESTER, SKILLS)
values (101, '2', 'C, CPP');
insert into student_skills (STUDENT_ID, SEMESTER, SKILLS)
values (102, '1', 'CPP, Java');
insert into student_skills (STUDENT_ID, SEMESTER, SKILLS)
values (102, '2', 'Java, JavaScript');
commit;
uj5u.com熱心網友回復：
一種選擇是將技能分成行（tempCTE），然后將它們聚合回來（第 11 行）：
SQL> with temp as
2 (select distinct
3 student_id,
4 trim(regexp_substr(skills, '[^,] ', 1, column_value)) skill
5 from student_skills cross join
6 table(cast(multiset(select level from dual
7 connect by level <= regexp_count(skills, ',') 1
8 ) as sys.odcinumberlist))
9 )
10 select student_id,
11 listagg(skill, ', ') within group (order by skill) skills
12 from temp
13 group by student_id;
STUDENT_ID SKILLS
---------- ------------------------------
101 C, CPP, SQL
102 CPP, Java, JavaScript
SQL>
uj5u.com熱心網友回復：
如果您的輸入資料是第一范式，那么生活會容易得多……事實上，您必須先拆分字串。毫無疑問，這些逗號分隔的字串首先是通過聚合生成的；是不是不能恢復更早的階段，每行顯示一個技能？
假設您對此事沒有發言權，您必須先拆分輸入字串，然后才能再次進行重復資料洗掉和聚合。一種技巧是為此使用 JSON 函式，例如：
select student_id,
listagg(distinct skill, ', ')
within group (order by semester, ord) as skills
from student_skills,
json_table('["' || replace(skills, ', ', '","') || '"]', '$[*]'
columns (
skill path '$',
ord for ordinality
)
)
group by student_id
order by student_id
;
uj5u.com熱心網友回復：
DISTINCT您可以在提取以逗號分隔的子字串時將子查詢與子句一起使用，然后應用LISTAGG()函式來重新組合這些片段，例如
WITH s AS
(
SELECT DISTINCT student_id, REGEXP_SUBSTR(skills,'[^,] ',1,level) AS skills
FROM student_skills
CONNECT BY level <= REGEXP_COUNT(skills,',') 1
AND PRIOR student_id = student_id
AND PRIOR sys_guid() IS NOT NULL
)
SELECT student_id, LISTAGG(skills,', ') WITHIN GROUP (ORDER BY 0) AS skills
FROM s
GROUP BY student_id
Demo
uj5u.com熱心網友回復：
您可以使用 XPATH 函式來查找不同的值：
SELECT student_id,
XMLQUERY(
'string-join(distinct-values(//text()), ", ")'
PASSING XMLTYPE(skills)
RETURNING CONTENT
).getStringVal() AS skills
FROM (
SELECT student_id,
'<b>'
|| LISTAGG('<a>' || REPLACE(skills, ', ', '</a><a>') || '</a>')
WITHIN GROUP (ORDER BY semester)
|| '</b>' AS skills
FROM student_skills
GROUP BY
student_id
);
其中，對于樣本資料，輸出：
學生卡 技能 101 C、SQL、CPP 102 CPP、Java、JavaScript
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/466325.html
上一篇：Oracle中的回圈記錄