面試過程中關於Oracle的查詢

　　這是最近在面試過程中遇到的幾個關於資料庫方面的試題，感覺還是很不錯的，經常在項目中也會遇到。

　　第一題：有三張表使用者表（users），角色表（role），使用者角色表（userroles），要求查詢顯示的結果如下　

    

     建立表

View Code

 1 /*建立使用者表*/ 2 create table USERS 3 ( 4   USERID   NUMBER(4) not null, 5   USERNAME NVARCHAR2(30) 6 ) 7  8 /*建立角色表*/ 9 create table ROLE10 (11   ROLEID   NUMBER(2) not null,12   ROLENAME NVARCHAR2(30)13 )14 15 /*建立使用者角色關係表*/16 create table USERROLES17 (18   USERID NUMBER(4),19   ROLEID NUMBER(2)20 )

　　通過兩種方法可以實現：

View Code

 1 /*方法一，使用分組函數*/ 2 SELECT W.USERNAME, MAX(DECODE(T.ROLEID, 1, '管理員', NULL)) || ',' || MAX(DECODE(T.ROLEID, 2, '普通使用者', NULL)) AS "角色" 3   FROM USERS W, ROLE T, USERROLES S 4  WHERE W.USERID = S.USERID 5    AND T.ROLEID = S.ROLEID 6  GROUP BY W.USERNAME 7   8 /*方法二，使用oracle內建函數 WMSYS.WM_CONCAT */ 9 SELECT P.USERNAME,10        DECODE(SUBSTR(P.ROS, 0, 1), 1, '管理員') || ',' ||11        DECODE(SUBSTR(P.ROS, 3, 1), 2, '普通使用者')12   FROM (SELECT W.USERNAME, WMSYS.WM_CONCAT(T.ROLEID) ROS13           FROM USERS W, ROLE T, USERROLES S14          WHERE W.USERID = S.USERID15            AND T.ROLEID = S.ROLEID16          GROUP BY W.USERNAME) P

　　

　　第二題：有一張沒有主外鍵關係的資料表，刪除所有列相同的重複資料

　　1.查詢所有列相同的資料行

View Code

1 SELECT T.欄位1, T.欄位2, T.欄位3, COUNT(*)2   FROM TABLE_1 T3  GROUP BY T.欄位1, T.欄位2, T.欄位34 HAVING COUNT(*) > 1

　　2.刪除上述查詢的資料結果

View Code

1 DELETE FROM TABLE_1 S2  WHERE S.欄位1, S.欄位2, S.欄位3 IN ( /*查詢1語句*/3        )

這樣就可以刪除表中所有列相同的重複資料，如果記錄多的話刪除會比較慢。

建議：先將合格資料記錄(即為要刪除的資料庫紀錄)存取到一個臨時資料表中，然後再進行刪除時就不用進行查詢了，提高了資料庫的刪除速度。

大家都可能會問，這樣刪除肯定會存在一個問題，我們不是把所有重複的資料刪掉了嗎？而我們想保留重複紀錄中最新一條的記錄，該如何?呢！

　　在Oracle中，有個隱藏的列自動rowid，裡面會給每條記錄唯一的rowid，如果我們想儲存最新的一條記錄，我們就可以利用這個欄位，儲存重複資料最大的rowid就可以實現了。

View Code

1 SELECT A.ROWID, 欄位1, 欄位22   FROM TABLE_1 A3  WHERE A.ROWID != (SELECT MAX(B.ROWID)4                      FROM TABLE_1 B5                     WHERE A.欄位1 = B.欄位16                       AND A.欄位2 = B.欄位2)

　　這樣就可以把所有的重複記錄(rowid不是最大)尋找出來。裡面的SQL語句是尋找出rowid最大的重複資料記錄，而外面的是除去rowid最大外的其他重複資料記錄。當我們要刪除這些記錄時，可以使用

View Code

1 DELETE FROM TABLE_1 A2  WHERE A.ROWID IN A.ROWID != (SELECT MAX(B.ROWID)3                                 FROM TABLE_1 B4                                WHERE A.欄位1 = B.欄位15                                  AND A.欄位2 = B.欄位2)

 

其實還有一道題，就是關於學生、老師、課程、成績這幾張表之間的關係查詢，大部分人應該見過，有空還是需要好好的熟練。

今天就到此為止吧，如果大家還有其它好的方法，可以拿出來一塊分享，供大家一塊學習、交流！！！