order by case when 语句

• 通过CASE语句为不同条件赋予排序权重值，实现按自定义顺序对查询结果排序。
• 案例:
  • 创建一张成绩表

    CREATE TABLE academic_records (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
        student_id VARCHAR(10) NOT NULL COMMENT '学生唯一标识符',
        student_name VARCHAR(50) NOT NULL COMMENT '学生姓名',
        subject VARCHAR(20) NOT NULL COMMENT '学科名称',
        score DECIMAL(5,1) NOT NULL COMMENT '数值成绩（0-100分，允许一位小数）',
        CHECK (score BETWEEN 0 AND 100)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生学业成绩记录表';
    

  • 填充数据

    -- 关闭自动提交，保证事务一致性
    SET autocommit = 0;
    
    -- 插入核心数据
    INSERT INTO academic_records (student_id, student_name, subject, score)
    SELECT 
      students.student_id,
      students.student_name,
      subjects.subject,
      ROUND(RAND() * 90 + 10, 1) AS score -- 生成10-100之间的随机成绩
    FROM 
      (-- 生成10个学生基础信息
       SELECT 'S2023001' AS student_id, '张一鸣' AS student_name UNION ALL
       SELECT 'S2023002', '李思思' UNION ALL
       SELECT 'S2023003', '王伟' UNION ALL
       SELECT 'S2023004', '赵敏' UNION ALL
       SELECT 'S2023005', '陈强' UNION ALL
       SELECT 'S2023006', '周晓晓' UNION ALL
       SELECT 'S2023007', '黄轩' UNION ALL
       SELECT 'S2023008', '吴倩' UNION ALL
       SELECT 'S2023009', '郑浩' UNION ALL
       SELECT 'S2023010', '林小美'
      ) students
    CROSS JOIN 
      (-- 定义三门学科
       SELECT '语文' AS subject UNION ALL
       SELECT '数学' UNION ALL
       SELECT '英文'
      ) subjects;
    
    -- 提交事务
    COMMIT;
    

  • 查询

    SELECT * FROM
    academic_records a
    ORDER BY
    	CASE  
    	WHEN a.`subject` = '语文' THEN 1  
    	WHEN a.`subject` = '数学' THEN 2  
    	ELSE 3  
    	END;
    

    •