mysql order by case when

mysql order by case when的用法

需求

对一个年级的学生按班级进行排序，按班级从小到大排序，如果班级为空排在最后

代码

SELECT    @ROW := @ROW + 1 AS rowno,    ID,    MOBILE_NO,    FIRST_NAME,    MEM_CLASS,    enrollment_year,    DATE_FORMAT(joinclass_date, '%Y-%m-%d') joinclass_date,    NIKE_NAME,    LOGIN_NAMEFROM    T_MEMBER,    (SELECT @ROW := 0) rWHERE    DELETED = 'N'AND MEM_TYPE = 1AND (    MEM_CLASS IS NULL    OR MEM_CLASS = ''    OR MEM_CLASS NOT IN (1, 2, 3, 4, 5, 6))AND SCHOOLID = 11839AND enrollment_year = 2016ORDER BY    CASEWHEN ifnull(MEM_CLASS, '') = '' THEN    0ELSE    1END DESC, - MEM_CLASS DESC

解读

由于MEM_CLASS 字段被设置成了字符串类型，但我需要按数字从小到大排序，所以MEM_CLASS 前面加个负号“-”，将字符转成数字

下面是重点

其实是个组合排序ORDER BY    -- 先按CASE WHEN的结果（ifnull(MEM_CLASS, '') = '' THEN   0 ELSE  1 END）即0和1进行降序排列    CASE WHEN   ifnull(MEM_CLASS, '') = '' THEN 0 ELSE  1 END DESC,    -- 再按‘- MEM_CLASS’进行降序排列（即数字升序排列）     - MEM_CLASS DESC