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', '-01-11');INSERT INTO `customsort` VALUES ('Apply', '-01-11');INSERT INTO `customsort` VALUES ('Review', '-01-01');INSERT INTO `customsort` VALUES ('Finish', '-01-14');INSERT INTO `customsort` VALUES ('Apply', '-01-01');INSERT INTO `customsort` VALUES ('Review', '-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 FROMcustomsort
给status赋值有排序大小的type
自定义排序SQL
SELECT STATUS,date1 FROMcustomsort WHERESTATUS 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;