记录mysql排序中遇到的问题

前言

今天开发过程中遇到一个分页查询的需求：表A中将状态1的放到列表前面，其余三种状态2，3，4放到列表后面，也就大体上组成了两部分1+2，3，4。于此同时，这两部需要按照各自的创建时间从大到小排序。表结构如下：

name	status	create_time
a	1	2
b	3	3
c	1	1
d	2	5
e	4	4

那么按照需求，排序结束的结果应该是：

name	status	create_time
a	1	2
c	1	1
d	2	5
e	4	4
b	3	3

方法一：UNION

首先思路就是将两部分分别查询出来，然后通过UNION联合起来，sql语句如下：

select * from A where status = 1 order by create_time desc
union
select * from A where status != 1 order by create_time desc

结果：报错，原因就是UNION关键字的前后如果要加order by则只能存在一个，改进写法：

select table1.* from (select * from A where status = 1 order by create_time desc) table1
union
select table2.* from (select * from A where status != 1 order by create_time desc) table2

结果：没报错，但是排序不对。原因：在子语句中如果都使用了排序关键字，但是没有通过limit关键字进行限制，则会被mysql优化器取消排序字段，导致排序失效。

根据这个原因，我们可以加上limit关键字：

select
	table1.*
from
	(
	select
		*
	from
		A
	where
		status = 1
	order by
		create_time desc
	limit 9999) table1
union
select
	table2.*
from
	(
	select
		*
	from
		A
	where
		status != 1
	order by
		create_time desc
	limit 9999) table2

结果：是我们需要的结果！
但是！这种limit条件未免也太暴力了，很明显在数据量大的时候会出大问题。

那么还有其他更好的方式吗？

有！

方法二：mysql的CASE函数

我们可以通过CASE函数达到自定义排序的需求。我们将sql语句修改如下：

select
	*
from
	A
order by
	case
		status when 1 then 0
		else 1
	end,
	create_time desc

通过以上方式，我们以更加简洁、优雅的方式实现了mysql的自定义排序，完成了需求。