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

杨建荣 2019-12-16 11:18:54
作者介绍

杨建荣,dbaplus社群联合发起人,竞技世界资深DBA,前搜狐畅游数据库专家,Oracle ACE,腾讯云TVP。具有十多年数据库开发和运维经验,目前专注于开源技术、运维自动化和性能调优。《Oracle/MySQL DBA工作笔记》作者,每天通过微信、博客进行技术分享,已连续坚持2100多天。

 

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

 

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

 

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这样的频率的,所以在这种模式下是得不到所需的结果的。

 

 

 

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

 

 

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

 

 

 

观察列表,我们可以看到对于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)

活动预告