大笨鳥的私房菜

這次的專案遇到了一個之前沒碰過一需求：「依符合條件的數量來做排序」

舉例來說：
　　我們要用「密碼」、「角色代碼」、「工作代碼」與「組職代碼」來搜尋符合條件的使用者，但這四個條件中，最低只要符合「角色代碼」與「工作代碼」即可，而在排序時，完全符合四個條件的使用者排在前面，符合三個條件的使用者排中間，只符合兩個條件的則排最後，所以最後查出來的結果應該是

符合四個條件的使用者
符合四個條件的使用者
　　　．．．
　　　．．．
符合三個條件的使用者
符合三個條件的使用者
　　　．．．
　　　．．．
符合兩個條件的使用者
符合兩個條件的使用者

本以為會有點兒麻煩，但其實透過 Oracle 的 DECODE Function 就簡單多了

SELECT USER_ID, 
       DECODE(PASSWORD, '12345678', 1, 0) +
       DECODE(ORG_ID  , 6         , 1, 0) AS SCORE
FROM (
    SELECT USER_ID, PASSWORD, ORG_ID
    FROM PM_USER_PROFILE
    WHERE ROLE_ID = 4 AND JOB_TYPE = 4
)
ORDER BY SCORE DESC

以上的 SQL 包含一個子查詢 (第 5 ~ 7 行)，其目的在於先查出符合最低要求的資料，之後，再針對這些資料進行處理，而處理的方式是用 Oracle 的 DECODE Function 來進行剩下條件的比對，符合的得 1 分，不然得 0 分，之後將這些條件得分加總成一個欄位後進行排序即可得到所需的結果。

當然上面的 SQL 也可以統合成一個 SQL，如下 (不建議使用)：

SELECT USER_ID, 
       DECODE(PASSWORD, '12345678', 1, 0) +
       DECODE(ORG_ID  , 6         , 1, 0) AS SCORE
FROM PM_USER_PROFILE
WHERE ROLE_ID = 4 AND JOB_TYPE = 4
ORDER BY SCORE DESC

但是這個 SQL 的最大缺點會在於當查詢 Table 的資料量越大時效能會越差。理由是這個 SQL 會先對 Table 中的所有 PASSWORD 與 ORG_ID 進行 DECODE Function 的運算後才會去執行 WHERE 的刪減，如此效能就會被拖慢了。而跟上面的 SQL 比起來，上面的 SQL 先用一個子查詢將符合最低要求的資料先抓出來，之後再對這些資料進行 DECODE Function 的運算，在數量上就遠比整個 Table 內的資料少了很多，因此在 Table 的資料量多的時候，上面的 SQL 效能會比較好。

另外，因為此處的 SQL 使用了 Oracle 的 Function，所以只限定用在 Oracle。但如果其他的資料庫也提供了與 DECODE 相同的 Function，那麼是可以替換掉 DECODE 之後用在別的資料庫上，所以在文章分類裏我將此篇文章歸類在 SQL 而不是 Oracle。

最後，以上的 SQL 可以統歸成：

SELECT 結果欄位一,..., 結果欄位 M, 
       DECODE(比對欄位一, 剩餘條件值一, 1, 0) +
       DECODE(比對欄位二, 剩餘條件值二, 1, 0) +
               .....
               .....
       DECODE(比對欄位 N, 剩餘條件值 N, 1, 0) AS SCORE
FROM (
    SELECT 結果欄位一,..., 結果欄位 M, 比對欄位一,比對欄位二,...,比對欄位 N
    FROM 表格
    WHERE 最低要求比對欄位一 = 最低要求條件值一 AND 
          最低要求比對欄位二 = 最低要求條件值二 AND 
                .....
                .....
          最低要求比對欄位 X = 最低要求條件值 X
)
ORDER BY SCORE DESC

P.S.：
   DECODE Function 的用法為

        DECODE(欄位, 比對條件值1, 符合條件要呈現的值1, 
                              比對條件值 2, 符合條件要呈現的值2,
                                               .....
                               比對條件值 N, 符合條件要呈現的值N,  不符合前列比對條件時要呈現的值)

   例：DECODE(PASSWORD, '12345678', 1, 0)，所代表的是 PASSWORD 欄位值若為 12345678 的話，此欄位傳回 1，不然傳回 0。