想請教關於MYSQL當中order by用case when排序兩個不同的欄位(已解決)

mysql

心累 2023-02-14 18:24:00 ‧ 807 瀏覽

分享至

2/15更新已解決
將距今時間以10為底取LOG取得不超過100的對數加上100~400的權重值

目前的解法:
換個類似的例子，班上有男有女並且成績落在1~100
依照及格男性>未及格男性>及格女性>非及格女性排序
及格(60分)的男性以年紀從小到大排序
未及格男性以成績排序
及格女性以身高排序
未及格的女性以學號排序

case
when  及格的男性   then 100 + LOG10(age) 
when  未及格的男性 then 200 + LOG10(score)
when  及格的女性   then 300 + LOG10(height)
when  未及格的女性 then 400 + LOG10(id) 
end

這是我自己測試賽事排序的SQL，實際欄位已經調整但意思相同，為了方便這裡將以A-E(賽事報名前、報名開使、賽事開始、報名結束、賽事結束)當作when的條件，目前order by的部分是先將A-E的條件去做排序，再以距離目前時間做排序，最後再依據各別的欄位做排序

希望能夠改成先以A-E排序的時候再依據時間排序(同時)

我有試過將第一個case when的1、2、3、4調整到1000000~4000000 + 距離目前時間，不過這個方式只是暫時而不算長久之計

有沒有大神可以提供更好的方式

PPTaiwan iT邦好手 1 級 ‧ 2023-02-14 18:41:05 檢舉

建議你將 TSQL 轉出來給大家

心累 iT邦新手 5 級 ‧ 2023-02-15 00:10:33 檢舉

補充一下我是用MySQL

堡哥 iT邦新手 5 級 ‧ 2023-02-15 13:59:14 檢舉

建議先在select產生要排序的欄位(用case判斷)
以上例來說應該是會有兩個排序欄位。
(A-E及日期，名稱分別為orderField01, orderField02)
寫成VIEW(vCompetition)，接著再將此VIEW做order by
(例如 select * from vCompetition order by orderField01, orderField02)

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

ckp6250

iT邦好手 1 級 ‧ 2023-02-14 20:00:50

題外話，
now() 函數用這麼多次，肯定效率不彰，
萬一這個Table很大，數百千萬筆，要執行很多秒的話，
答案可能不如您的想像。

應該把now()變成變數之後，再帶進 select 中

select
    (@now > xxx ) as `rrr`
from competitopn c,(select @now := now() ) n

回應 1
分享
檢舉

心累 iT邦新手 5 級 ‧ 2023-02-15 00:01:19 檢舉

NOW()是回傳執行時的TIMESTAMP變數，不會影響效能
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_now

登入發表回應

JamesDoge

iT邦高手 1 級 ‧ 2023-02-15 06:02:20

若要先依據 A-E 排序，再依據時間排序，可以將兩個排序條件都納入到同一個 CASE WHEN 子句中，例如：

SELECT
    CASE
        WHEN (now() > c.registration_start_at AND now() < c.registration_end_at)
            THEN 1
        WHEN (now() < c.registration_start_at)
            THEN 2
        WHEN (now() > c.competition_start_at AND c.is_completed = false)
            THEN 3
        WHEN (now() > c.registration_end_at)
            THEN 4
    END AS status_order,
    CASE
        WHEN (now() > c.registration_start_at AND now() < c.registration_end_at)
            THEN ABS(TIMESTAMPDIFF(HOUR, c.registration_end_at, NOW()))
        WHEN (now() > c.competition_start_at AND c.is_completed = false)
            THEN ABS(TIMESTAMPDIFF(HOUR, c.competition_start_at, NOW()))
        WHEN (c.is_completed = true)
            THEN ABS(TIMESTAMPDIFF(HOUR, c.completed_at, NOW()))
        ELSE ABS(TIMESTAMPDIFF(HOUR, NOW(), c.registration_start_at))
    END AS time_order,
    c.*
FROM competition c
ORDER BY status_order, time_order;