MySQL - 排序 - hoyo 學習紀錄

1-1-1 排序

ORDER BY CHAR_LENGTH(question_code) DESC, question_code DESC;

1	ORDER BY CHAR_LENGTH(question_code) DESC, question_code DESC;

CONVERT(name using big5)

1	CONVERT(name using big5)

預設 UTF-8 的排序，不是台灣人習慣的排序，台灣人習慣的排序是按照筆劃順序，所以排序時須轉換成 big5

SELECT id, organization_name FROM organization WHERE status=1 ORDER BY CONVERT( organization_name USING big5 )

1	SELECT id, organization_name FROM organization WHERE status=1 ORDER BY CONVERT( organization_name USING big5 )

ORDER BY CAST(name AS UNSIGNED) DESC

1	ORDER BY CAST(name AS UNSIGNED) DESC

ORDER BY FIELD(Language,'ENU','JPN','DAN'), ID

1	ORDER BY FIELD(Language,'ENU','JPN','DAN'), ID

ORDER BY CASE 
    WHEN name LIKE "%John%" THEN 1 
    WHEN name LIKE "%Doe%"  THEN 2 
    ELSE 3 
END

ORDER BY CASE

WHEN name LIKE "%John%" THEN 1

WHEN name LIKE "%Doe%" THEN 2

ELSE 3

END

ORDER BY 
    SUBSTRING(exam_source, 5, 3) DESC,
    SUBSTRING(exam_source, 9, 1) DESC;

ORDER BY

SUBSTRING(exam_source, 5, 3) DESC,

SUBSTRING(exam_source, 9, 1) DESC;

字串的擷取有多個 function 請根據需求取用

3,162 total views, 1 views today