SQL自定义排序规则

Apply：提交请求
Review：等待回复
Finish：任务完成
按日期和状态排序，先状态为：提交请求（Apply）—按时间倒序，然后状态为：等待回复（Review）—按时间倒序，然后状态为：任务完成（Finish）—按时间倒序。

DROp TABLE IF EXISTS `customsort`;
CREATE TABLE `customsort` ( `status` VARCHAr ( 255 ), `date1` date );
INSERT INTO `customsort` VALUES ('Review', '2022-01-11');
INSERT INTO `customsort` VALUES ('Apply', '2022-01-11');
INSERT INTO `customsort` VALUES ('Review', '2022-01-01');
INSERT INTO `customsort` VALUES ('Finish', '2022-01-14');
INSERT INTO `customsort` VALUES ('Apply', '2022-01-01');
INSERT INTO `customsort` VALUES ('Review', '2022-01-05');

使用 case when 条件 then 结果 ........ end

SELECT STATUS
	,
	date1,
	( CASE WHEN STATUS = 'Apply' THEN 0 WHEN STATUS = 'Review' THEN 1 WHEN STATUS = 'Finish' THEN 2 ELSE 3 END ) type 
FROM
	customsort

给status赋值有排序大小的type

SELECt STATUS
	,
	date1 
FROM
	customsort 
WHERe
	STATUS IN ( 'Apply', 'Review', 'Finish' ) 
ORDER BY
	( CASE WHEN STATUS = 'Apply' THEN 0 WHEN STATUS = 'Review' THEN 1 WHEN STATUS = 'Finish' THEN 3 ELSE 4 END ),
	date1 DESC;