爆资讯爆资讯爆资讯

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

作者介绍

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

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

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

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

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

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

未经允许不得转载:爆资讯 » 一条看似不合理SQL和10个合理的解释

相关推荐