MySQL 自訂欄位排列順序

MySQL 中的 ORDER BY 可以針對欄位進行升序 (ASC) 或降序 (DESC) 排序,例如數字從小到大,或者英文字母從 Z 到 A 等。不過有時難免會有需要自訂順序的需求,列如希望排列的數字順序是 3, 5, 1, 2。

早先會利用 IF 搭配 AS 來解決這類問題:
SELECT `id`, IF(`state` = 3, 1, IF(`state` = 5, 2, IF(`state` = 1, 3, IF(`state` = 2, 4, 5)))) AS ORDERRES FROM `table` ORDER BY ORDERRES;

但是如果搭配 MySQL 的 FIELD 函式,這個需求能更輕易的被實現:
SELECT `id` FROM `table` ORDER BY FIELD(`state`, 3, 5, 1, 2);
按照 MySQL 官方文件的說明, FIELD 函式將會回傳第一個參數在後面其他參數中的索引位置,例如: SELECT FIELD(1, 3, 2, 1); 將會回傳 3 ;如果第一個參數沒有在後面其他參數出現過將會回傳 0 。

上面兩個方法其實回傳的結果未必相同,如果資料表 `table` 內有 `state` 為 4 的資料列,使用 IF 將會被排列在最後,而使用 FIELD 的作法則會被排在最前面。這是因為最裡面的 IF 指名 `state` 不是 3, 5, 1, 2 的欄位都回傳 5 ,而 FIELD 卻預設回傳 0 。

因此我們需要稍微修改一下 FIELD 的查詢命令,其一是利用 IF 判斷:
SELECT `id`, FIELD(`state`, 3, 5, 1, 2) AS ORDERRES FROM `table` ORDER BY IF(ORDERRES  = 0, 5, ORDERRES  )
這樣,不在 FIELD 其他參數內的 `state` 就會排到比較後面。

除了 IF 之外也可以將 FIELD 內的參數反向排列,再配合 DESC 也會有一樣的效果:
SELECT `id` FROM `table` ORDER BY FIELD(`state`, 2, 1, 5, 3) DESC

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

數學難題 * 限制時效已用盡。請重新載入驗證碼。