- 通过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;
- 创建一张成绩表
10-27
1230

06-08
2250

06-20