实例1
原始数据顺序
需要的效果:
- 学科按照顺序 语文,数学,英语
- 分数倒序
演示
- 创建表
CREATE TABLE `student_score` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`student_id` bigint(20) DEFAULT NULL COMMENT '学生ID',
`score` decimal(4,1) DEFAULT NULL COMMENT '分数',
`subject` varchar(64) DEFAULT NULL COMMENT '学科',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='学生分数表';
- 加数据
INSERT INTO `student_score`(`id`, `student_id`, `score`, `subject`) VALUES (1, 1, 50.0, '语文');
INSERT INTO `student_score`(`id`, `student_id`, `score`, `subject`) VALUES (2, 1, 60.0, '数学');
INSERT INTO `student_score`(`id`, `student_id`, `score`, `subject`) VALUES (3, 1, 89.0, '英语');
INSERT INTO `student_score`(`id`, `student_id`, `score`, `subject`) VALUES (4, 2, 20.0, '语文');
INSERT INTO `student_score`(`id`, `student_id`, `score`, `subject`) VALUES (5, 2, 30.0, '数学');
INSERT INTO `student_score`(`id`, `student_id`, `score`, `subject`) VALUES (6, 2, 10.0, '英语');
INSERT INTO `student_score`(`id`, `student_id`, `score`, `subject`) VALUES (7, 3, 90.0, '语文');
INSERT INTO `student_score`(`id`, `student_id`, `score`, `subject`) VALUES (8, 3, 99.0, '数学');
INSERT INTO `student_score`(`id`, `student_id`, `score`, `subject`) VALUES (9, 3, 91.0, '英语');
使用order by case when语法查询
-- 需求: 1. 语文排最前面,数学排中间,英语排最后
-- 2. 并且按分数降序排列
SELECT * FROM student_score ORDER BY
case
when subject = '语文' then 1
when subject = '数学' then 2
else 3
end
asc, score desc;
结果:
变形理解
-- 等同如下语句
SELECT *,
case
when subject = "语文" then 1
when subject = "数学" then 2
else 3
end as subject_sort
FROM student_score
ORDER BY subject_sort asc , score desc;
结果;
使用order by if语句变形,结果一样
SELECT
*
FROM
student_score
ORDER BY
IF(`subject` = '语文',1,
IF( `subject` = '数学', 2, 3 )
),
score DESC
案例2
需要的效果:
- subject按照顺序 语文,数学,英语 排
- subject=“语文“时 ,score 升序排
- subject=“数学“时 ,score 倒序排
- subject=“英语“时 ,student_id 升序排
sql如下
SELECT * FROM student_score ORDER BY
case subject
WHEN '语文' THEN 1
WHEN '数学' THEN 2
ELSE 3
end asc,
CASE subject WHEN '语文' THEN score end asc,
CASE subject WHEN '数学' THEN score end desc,
CASE subject WHEN '英语' THEN student_id end asc;