mysql 按条件排序：order by 高级用法之case when, if 复杂排序

转载自：https://blog.csdn.net/weixin_44684303/article/details/124445293

实例1

原始数据顺序

需要的效果:

1. 学科按照顺序 语文,数学,英语
2. 分数倒序
   

演示

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='学生分数表';

 

• 1
• 2
• 3
• 4
• 5
• 6
• 7

2. 加数据

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, '英语');

 

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

使用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;

 

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

结果:

变形理解

-- 等同如下语句
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;

 

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

结果;

使用order by if语句变形，结果一样

SELECT
	* 
FROM
	student_score 
ORDER BY
IF(`subject` = '语文',1,
	IF( `subject` = '数学', 2, 3 )
	),
	score DESC

 

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

案例2

需要的效果：

1. subject按照顺序 语文,数学,英语 排
2. subject=“语文“时 ，score 升序排
3. subject=“数学“时 ，score 倒序排
4. 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;

 

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

结果