最近在项目中遇到一个比较有难度的sql，关于排序的，实现后还是觉得蛮有记录价值，所以便有了这篇博客。

需求

原始的排序需求是这样的，首先根据状态排序（进行中-未开始-已完赛），其次根据比赛时间排序，当状态为已完成时根据比赛时间倒序，状态为其他时根据比赛时间正序，其他规则不涉及这里的技术，就不详细说明了

分析：对于一阶排序，原有比赛状态存在多个状态为为进行中，且状态值的定义并不符合需求定义的方式进行升序或者倒序，这里排序就要用到order by case when 这种形式。同样的二阶排序涉及到根据不同状态进行时间正序和倒序，也可以使用order by case when进行排序，当然，在实现中遇到了各种问题，下面会一一讲解。

实现过程

根据上面的分析，我写出了我的初代排序sql如下（涉及公司表结构等内容对sql做了简化处理）

SELECT
	d.team1,
	d.team2,
	d.team1_score,
	d.team2_score 
FROM
	demo d 
ORDER BY
CASE
		WHEN d.demo_status = 1 THEN
		8 
		WHEN d.demo_status IN ( 2, 3, 4 ) THEN
		7 
		ELSE d.demo_status END,
CASE
		WHEN d.demo_status = 9 THEN
		d.demo_time DESC
		ELSE d.demo_time  END

以上sql在执行过程中是会报错的，原因就在于 case when结构内部不能使用mysql关键字，其原理就是将状态值进行转化后排序，所以只能是具体的值。于是，我作了转换，将比赛时间转化为时间戳，想要倒序的时候就根据当前时间和比赛时间的时间戳差值进行排序，这样就相当于把正序转化为了倒序，优化后的sql如下

SELECT
	d.team1,
	d.team2,
	d.team1_score,
	d.team2_score 
FROM
	demo d 
ORDER BY
CASE
		WHEN d.demo_status = 1 THEN
		8 
		WHEN d.demo_status IN ( 2, 3, 4 ) THEN
		7 
		ELSE d.demo_status END,
CASE
		WHEN d.demo_status = 9 THEN
		(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP( d.demo_time )) 
		ELSE d.demo_time  END

以上sql在执行后会发现，一阶排序是正常的，二阶排序会出现混乱，出现混乱的原因就是状态为9时根据时间差（数值）排序，非9时根据时间（时间戳）进行排序，时间差和时间戳的值范围和单位是不同的，所以这里我们就要统一排序依据，确保每个case语句分支返回的是相同类型的值，优化后的sql如下

SELECT
	d.team1,
	d.team2,
	d.team1_score,
	d.team2_score 
FROM
	demo d 
ORDER BY
CASE
		WHEN d.demo_status = 1 THEN
		8 
		WHEN d.demo_status IN ( 2, 3, 4 ) THEN
		7 
		ELSE d.demo_status END,
CASE
		WHEN d.demo_status = 9 THEN
		(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP( d.demo_time )) 
		ELSE UNIX_TIMESTAMP( d.demo_time )  END

其实到了此处，sql已经是基本正确的了，为什么是基本正确呢？因为有一个细节大家注意到没有，select的字段里没有表的主键，笔者这里因为实际用到的时候是关联查询，主表的主键id是不需要的，所以就没有返回，在这里其实就埋藏着一个坑点，因为大家在写sql的时候，很多情况都是有分页的，而mysql的分页逻辑里，是有根据排序规则去取相应索引和条数的数据的，当sql的排序规则进行排序无法判定先后顺序时，它会根据select中主表的id进行排序，这样每次分页都能取到不同的数据，而此处select的字段里没有将主表id查询出来，也就没有最后这段id排序，导致的后果就是前一页出现的数据，在这一页也可能会出现，这种重复出现是概率性的，有时候是这条数据，有时候是那条数据，有时候又不会重复，这样肯定是不行的，最终再对sql进行修改，最终版如下

SELECT
    d.id,
	d.team1,
	d.team2,
	d.team1_score,
	d.team2_score 
FROM
	demo d 
ORDER BY
CASE
		WHEN d.demo_status = 1 THEN
		8 
		WHEN d.demo_status IN ( 2, 3, 4 ) THEN
		7 
		ELSE d.demo_status END,
CASE
		WHEN d.demo_status = 9 THEN
		(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP( d.demo_time )) 
		ELSE UNIX_TIMESTAMP( d.demo_time )  END

d.id虽然不用，但在sql中依然需要查出来，当然也可以在末尾加一个主键id排序，这样就是一个正确的sql了

order by case when除了以上用法，还能把字段提出来放在case后面when后面跟具体值，因为这里涉及到多个状态合并为一个状态，所以写法上稍微有些不同，但都是可行的。

总结

其实也没啥好总结的，写代码本身就不是一件一帆风顺的事情，有些时候受限于眼界或者其他因素，不能一次性写出完美的代码（雷总除外，雷总代码如shi一般优雅），所以就需要我们不断去尝试，不断去找到问题的突破口，通过各种途径去补全我们的知识库，去完善我们的代码，也许多年后再回头看，那不正是我们的来时路，这也正是我创建这个博客网站的初衷，路漫漫其修远兮，吾将上下而求索。