LMS知識社群各系上網率圖資處首頁登入
位置: 鄭光盛 > 技術 > SQL
SQL指定排序示範
by 鄭光盛 2013-10-22 08:41:51, 回應(0), 人氣(5680)
以課程表 Course 為例
我們知道要把星期幾第幾節(欄位名稱 ClassNo) 依M1…ME、T1…TE、W1…WE、H1…HE、F1…FE、S1…SE 、U1…UE排序
可以寫成醬子

select * from
course
order by
(case course.ClassNo 
when 'M1' then '01'
when 'M2' then '02'
when 'M3' then '03'
when 'M4' then '04'
when 'M5' then '05'
when 'M6' then '06'
when 'M7' then '07'
when 'M8' then '08'
when 'M9' then '09'
when 'MA' then '10'
when 'MB' then '11'
when 'MC' then '12'
when 'MD' then '13'
when 'ME' then '14'
when 'T1' then '15'
when 'T2' then '16'
when 'T3' then '17'
when 'T4' then '18'
when 'T5' then '19'
when 'T6' then '20'
when 'T7' then '21'
when 'T8' then '22'
when 'T9' then '23'
when 'TA' then '24'
when 'TB' then '25'
when 'TC' then '26'
when 'TD' then '27'
when 'TE' then '28'
when 'W1' then '29'
when 'W2' then '30'
when 'W3' then '31'
when 'W4' then '32'
when 'W5' then '33'
when 'W6' then '34'
when 'W7' then '35'
when 'W8' then '36'
when 'W9' then '37'
when 'WA' then '38'
when 'WB' then '39'
when 'WC' then '40'
when 'WD' then '41'
when 'WE' then '42'
when 'H1' then '43'
when 'H2' then '44'
when 'H3' then '45'
when 'H4' then '46'
when 'H5' then '47'
when 'H6' then '48'
when 'H7' then '49'
when 'H8' then '50'
when 'H9' then '51'
when 'HA' then '52'
when 'HB' then '53'
when 'HC' then '54'
when 'HD' then '55'
when 'HE' then '56'
when 'F1' then '57'
when 'F2' then '58'
when 'F3' then '59'
when 'F4' then '60'
when 'F5' then '61'
when 'F6' then '62'
when 'F7' then '63'
when 'F8' then '64'
when 'F9' then '65'
when 'FA' then '66'
when 'FB' then '67'
when 'FC' then '68'
when 'FD' then '69'
when 'FE' then '70'
when 'S1' then '71'
when 'S2' then '72'
when 'S3' then '73'
when 'S4' then '74'
when 'S5' then '75'
when 'S6' then '76'
when 'S7' then '77'
when 'S8' then '78'
when 'S9' then '79'
when 'SA' then '80'
when 'SB' then '81'
when 'SC' then '82'
when 'SD' then '83'
when 'SE' then '84'
else '99'
end)

就會以XX排序

標籤: SQL