問題：
如下圖所示，如何用資料庫語言實作將 EXCL檔案中的圖一表格轉換成圖二。
注意點：1.‘所學科目’欄位中包含的科目個數不確定；
2.‘所學科目’欄位中各科目的分隔符有可能是逗號或者頓號；
3.‘所學科目’欄位中各科目前后有可能有空格；
uj5u.com熱心網友回復：
select distinct name,age,grade,regexp_substr(subject,'[^[:punct:]]+',1,level) subject
from test
connect by level<=regexp_count(subject,'[[:punct:]]')+1;
uj5u.com熱心網友回復：
這個方法有點笨，但是能實作create table T1
(
s_name VARCHAR2(10),
s_age NUMBER,
s_class VARCHAR2(10),
s_subject VARCHAR2(100)
);
INSERT INTO t1 VALUES ('小明', 10, '三年級', '語文、數學、音樂');
INSERT INTO t1 VALUES ('小紅', 11, '四年級', '美術、數學、古典音樂');
WITH a1 AS
(SELECT s_name, s_age, s_class, regexp_substr(s_subject, '[^、]+', 1, 1) s1,
regexp_substr(s_subject, '[^、]+', 1, 2) s2, regexp_substr(s_subject, '[^、]+', 1, 3) s3,
regexp_substr(s_subject, '[^?￠]+', 1, 4) s4
FROM t1)
SELECT s_name, s_age, s_class, s1
FROM a1
WHERE s1 IS NOT NULL
UNION ALL
SELECT s_name, s_age, s_class, s2
FROM a1
WHERE s2 IS NOT NULL
UNION ALL
SELECT s_name, s_age, s_class, s3
FROM a1
WHERE s3 IS NOT NULL
UNION ALL
SELECT s_name, s_age, s_class, s4 FROM a1 WHERE s4 IS NOT NULL ORDER BY s_name;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/74971.html
標籤：開發