oracle 資料庫開發面試題，當時筆試的時候一個沒做出來，現附原題及答案

標籤：style   blog   color   io   ar   資料   div   sp   log   

1、

ID
1
2
3
5
6
7
8
10
11
12
15

表名tt，用sql找出ID列中不連續的ID，例如其中沒有的4：

--建立表及資料CREATE TABLE tt(ID INTEGER);INSERT INTO tt SELECT 1 FROM dualUNION ALLSELECT 2 FROM dualUNION ALLSELECT 3 FROM dualUNION ALLSELECT 5 FROM dualUNION ALLSELECT 6 FROM dualUNION ALLSELECT 7 FROM dualUNION ALLSELECT 8 FROM dualUNION ALLSELECT 10 FROM dualUNION ALLSELECT 11 FROM dualUNION ALLSELECT 12 FROM dualUNION ALLSELECT 15 FROM dual;COMMIT;

 

--用到了connect by level 造資料WITH IT AS (SELECT LEVEL ID FROM DUAL CONNECT BY LEVEL <= (SELECT MAX(ID) FROM TT))SELECT A.ID  FROM IT A WHERE NOT EXISTS (SELECT 1 FROM TT B WHERE A.ID = B.ID)

 

2、

將錄入不規範的房間資訊整理成標準格式

不規範表（多個房間用逗號分割）

ID	ROOM
1	101,102
2	201,202,203
3	301
.......

 

 

 

 

 

規範表

ID	ROOM
1	101
1	102
2	201
2	202
2	203
3	301
......

 

 

 

 

 

 

 

 

--單行單列轉多行--建立表及資料create table ttt(id integer,room varchar2(200));insert into tttselect 1,‘101,102‘ from dualunion allselect 2,‘201,202,203‘ from dualunion allselect 3,‘301‘ from dual;commit;

SELECT DISTINCT ID,REGEXP_SUBSTR(room, ‘[^,]+‘, 1, LEVEL, ‘i‘) AS STR    FROM tttCONNECT BY LEVEL <= LENGTH(room) - LENGTH(REGEXP_REPLACE(room, ‘,‘, ‘‘))+1;

 

oracle 資料庫開發面試題，當時筆試的時候一個沒做出來，現附原題及答案