一条看似不合理SQL和10个合理的解释

有一天看到了一个开发同学提的问题，感觉蛮有意思，就稍花了些时间总结了下，问题描述如下：

开发的时候碰到类似这么一个问题：

mysql中有表：

create table dept(
    id    int auto_increment primary key comment 'id',
    level int comment '权重，1--普通员工，2--经理',
    name  varchar(15) comment '员工姓名'
)

有一天这个系统用了很长时间了，需要增加一个“副经理”的角色，但是因为1和2已经在很多地方写死了，只能在原来的基础上，将3设为“副经理”，并且设计表的人表示以后绝对不会修改这个表了

本人小白，做外包，所以没办法

差不多就是这样，遇到一个需求，是将表中数据按照职位大小进行排序

然后我是这么写的：

select * from
dept
order by (4-level)%3 desc

然后问题虽然是解决了，思路也是我自己想出来的，但是我想不通为什么这样可以运行

有没有前辈能指点一下，这种解决思路是一个算法，还是一个什么思路？有没有通用的可以抽取的地方可以学习？这个问题疑惑了我两个月了，希望可以得到解答

看到回复里面 绝大多数都在说明这个需求的不合理，我想试试其他的方法。

这个问题如果换成一个更有意思的问题，那就是：

那就是如何让MySQL识别出2比3大？

在此我给出10种解法，大家可以根据自己的情况进行补充，在总结中也得到了

多位同学的支持，他们分别是以下4位。

小叶，开发DBA,我的得力助手

汤勇，运维方向，通信行业，从事MySQL数据库开发+数据库维护

小蒙，开发DBA，光电通信，从事MySQL数据库开发

齐宇轩，北京，运维方向

方法1

   

在这里说是方法1是想说明这可能不是最好的方案，但是从规范角度来说是最好的。

我们可以新增一个字段，然后修改数据，按照新的字段排序。

alter table dept add new_level int default 1 ;
update dept set new_level=1 where level=1;
update dept set new_level=3 where level=2;
update dept set new_level=2 where level=3;

使用如下的SQL查询即可。

select id,level,name from dept order by new_level desc;

方法2

   

可以使用filed函数来进行排序。

mysql> SELECT * FROM dept
     ORDER BY FIELD(`level`, 2, 3, 1);
+----+-------+------+
| id | level | name |
+----+-------+------+
|  6 |     2 | ff   |
|  7 |     2 | gg   |
|  3 |     3 | cc   |
|  1 |     1 | aa   |
|  2 |     1 | bb   |
|  4 |     1 | dd   |
|  5 |     1 | ee   |
+----+-------+------+
7 rows in set (0.00 sec)

方法3

   

可以使用find_in_set函数来进行排序。

思路就是把2,3,1组合成一个字符串，然后在字符串中取字符的下标来变相排序。

SELECT * FROM `dept` order by find_in_set(`level`,'2,3,1');
+----+-------+------+
| id | level | name |
+----+-------+------+
|  6 |     2 | ff   |
|  7 |     2 | gg   |
|  3 |     3 | cc   |
|  1 |     1 | aa   |
|  2 |     1 | bb   |
|  4 |     1 | dd   |
|  5 |     1 | ee   |
+----+-------+------+

方法4

   

可以使用case when函数来进行排序。

 select * from
     dept
     order by (case when level=1 then 1 when level=2 then 3 when level=3 then 2 end ) desc;

方法5

   

可以构造子查询来进行排序，

里面使用的还是case when,当然还可以使用其他的逻辑

select a.* from dept a,
 (select id,name,(CASE WHEN level = '1'
     THEN 'a'
   WHEN '2'
     THEN 'c'
   WHEN '3'
     THEN 'b' ELSE '0' end) as t  from dept ) b where a.id =b.id order by b.t desc;

方法6

   

可以使用union/union all函数来进行排序。

这种思路看起来有些取巧，但是不失为一种可扩展的方法

select * from dept where level=2
    union 
    select * from dept where level=3
    union 
    select *from dept where level=1

接下来我们做几种略微复杂的方法

方法7

   

在这里使用Hash的方法来解决，在问题中已经给出了：

(4-level)%3

我尝试了如下的方式，对于(x+N)%5,对于x为（1,2，3）可以得到如下的表格。可以看到顺序都是按照012012这样的频率的，所以在这种模式下是得不到所需的结果的。

x mod 3	x+1	x+2	x+3	x+4	x+5
1	2	0	1	2	0
2	0	1	2	0	1
3	1	2	0	1	2

我们把0,1,2当做手环上面的3个数，既然它们可以正向旋转，也可以反向旋转，所谓的反向旋转，输出的频率即为：210210

于是我得到了如下的表格：

x mod 3	3-x	4-x	5-x	6-x	7-x	8-x	9-x	10-x
1	2	0	1	2	0	1	2	0
2	1	2	0	1	2	0	1	2
3	0	1	2	0	1	2	0	1

观察列表，我们可以看到对于4-x,7-x,10-x都是按照021的顺序来排列的。

所以我们可以使用4-x,7-x,10-x得到如下的同样结果：

mysql> select (4-level)%3,(7-level)%3,level,id from
    ->       dept;
+-------------+-------------+-------+----+
| (4-level)%3 | (7-level)%3 | level | id |
+-------------+-------------+-------+----+
|           0 |           0 |     1 |  1 |
|           0 |           0 |     1 |  2 |
|           1 |           1 |     3 |  3 |
|           0 |           0 |     1 |  4 |
|           0 |           0 |     1 |  5 |
|           2 |           2 |     2 |  6 |
|           2 |           2 |     2 |  7 |
+-------------+-------------+-------+----+
7 rows in set (0.00 sec)

方法8

   

我们可以得到一个大胆的结果，那就是对于3*N+1，这种方式都是可行的。

先输出level和id的结果：

mysql> select (4-level)%3,level,id from
      dept;
+-------------+-------+----+
| (4-level)%3 | level | id |
+-------------+-------+----+
|           0 |     1 |  1 |
|           0 |     1 |  2 |
|           1 |     3 |  3 |
|           0 |     1 |  4 |
|           0 |     1 |  5 |
|           2 |     2 |  6 |
|           2 |     2 |  7 |
+-------------+-------+----+

按照我们刚总结的方式，我们可以得到3N+1的模式都可以输出结果，我们给出一个自负的测试结果，那就是对于任何大于0的数，测试结果幂等，所以我们构造了一个随机数，当然这种方式需要注意的是，还需要对id进行升序排列，否则输出结果还是有些差异。

mysql> select * from
    ->      dept
    ->      order by (3*(FLOOR(RAND()*100)+1)+1-level)%3 desc,id;
+----+-------+------+
| id | level | name |
+----+-------+------+
|  6 |     2 | ff   |
|  7 |     2 | gg   |
|  3 |     3 | cc   |
|  1 |     1 | aa   |
|  2 |     1 | bb   |
|  4 |     1 | dd   |
|  5 |     1 | ee   |
+----+-------+------+

方法9

   

我们可以根据函数的思想来进行分析，我们设想得到一个图形。

对于1,2,3分别对应f(1)<f(2)>f(3)，这显然是一个绝对值函数。

如果我们想得到一个精确的值，可以设置函数为y=a|x-2|+bx+c

带入1,2,3我们会得到如下的三个等式：

• 2b+c=3

• a+b+c=1

• a+3b+c=2

验算得到，a=-3/2, b=1/2, c=2

我们可以使用如下的三个函数来进行验证。

mysql> select -3*abs(-1)/2+1/2+2; 
+--------------------+
| -3*abs(-1)/2+1/2+2 |
+--------------------+
|             1.0000 |
+--------------------+
1 row in set (0.00 sec)


mysql> select -3*abs(2-2)/2+2/2+2;
+---------------------+
| -3*abs(2-2)/2+2/2+2 |
+---------------------+
|              3.0000 |
+---------------------+
1 row in set (0.00 sec)


mysql> select -3*abs(1)/2+3/2+2;  
+-------------------+
| -3*abs(1)/2+3/2+2 |
+-------------------+
|            2.0000 |
+-------------------+
1 row in set (0.00 sec)

所以这个SQL就呼之欲出了。

select * from
     dept
     order by -3*abs(level-2)/2+(level)/2+2  desc;

方法10

   

方法8的演进版本，我们可以考虑拟合，即不用精确值。

我们可以这样设想如果x=2为中位值，那么f(1)=f(3),

在f(1)<f(2)>f(3)的情况下，f(1)到f(2)单调递增，而f(2)>f(3)使得单调递减。

这个图形我们不需要关注它的函数值到底是多少，而是着重于考虑中位值，而要单调递减，则x需要在2~2.5之间。

mysql>  select * from
    ->      dept
    ->      order by -abs(level-2.1)  desc;
+----+-------+------+
| id | level | name |
+----+-------+------+
|  6 |     2 | ff   |
|  7 |     2 | gg   |
|  3 |     3 | cc   |
|  1 |     1 | aa   |
|  2 |     1 | bb   |
|  4 |     1 | dd   |
|  5 |     1 | ee   |
+----+-------+------+
7 rows in set (0.00 sec)

   

有道无术，术可成；有术无道，止于术

欢迎大家关注Java之道公众号

好文章，我在看❤️