有一天看到了一個(gè)開(kāi)發(fā)同學(xué)提的問(wèn)題，感覺(jué)蠻有意思，就稍花了些時(shí)間總結(jié)了下，問(wèn)題描述如下：

開(kāi)發(fā)的時(shí)候碰到類似這么一個(gè)問(wèn)題：

mysql中有表：

create table dept(id int auto_increment primary key comment 'id',level int comment '權(quán)重，1--普通員工，2--經(jīng)理',name varchar(15) comment '員工姓名' )

有一天這個(gè)系統(tǒng)用了很長(zhǎng)時(shí)間了，需要增加一個(gè)“副經(jīng)理”的角色，但是因?yàn)?和2已經(jīng)在很多地方寫死了，只能在原來(lái)的基礎(chǔ)上，將3設(shè)為“副經(jīng)理”，并且設(shè)計(jì)表的人表示以后絕對(duì)不會(huì)修改這個(gè)表了

本人小白，做外包，所以沒(méi)辦法

差不多就是這樣，遇到一個(gè)需求，是將表中數(shù)據(jù)按照職位大小進(jìn)行排序

然后我是這么寫的：

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

然后問(wèn)題雖然是解決了，思路也是我自己想出來(lái)的，但是我想不通為什么這樣可以運(yùn)行

有沒(méi)有前輩能指點(diǎn)一下，這種解決思路是一個(gè)算法，還是一個(gè)什么思路？有沒(méi)有通用的可以抽取的地方可以學(xué)習(xí)？這個(gè)問(wèn)題疑惑了我兩個(gè)月了，希望可以得到解答

看到回復(fù)里面 絕大多數(shù)都在說(shuō)明這個(gè)需求的不合理，我想試試其他的方法。

這個(gè)問(wèn)題如果換成一個(gè)更有意思的問(wèn)題，那就是：

那就是如何讓MySQL識(shí)別出2比3大？

在此我給出10種解法，大家可以根據(jù)自己的情況進(jìn)行補(bǔ)充，在總結(jié)中也得到了

多位同學(xué)的支持，他們分別是以下4位。

小葉，開(kāi)發(fā)DBA,我的得力助手

湯勇，運(yùn)維方向，通信行業(yè)，從事MySQL數(shù)據(jù)庫(kù)開(kāi)發(fā)+數(shù)據(jù)庫(kù)維護(hù)

小蒙，開(kāi)發(fā)DBA，光電通信，從事MySQL數(shù)據(jù)庫(kù)開(kāi)發(fā)

齊宇軒，北京，運(yùn)維方向

方法1

? ?

在這里說(shuō)是方法1是想說(shuō)明這可能不是最好的方案，但是從規(guī)范角度來(lái)說(shuō)是最好的。

我們可以新增一個(gè)字段，然后修改數(shù)據(jù)，按照新的字段排序。

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函數(shù)來(lái)進(jìn)行排序。

mysql> SELECT * FROM deptORDER 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函數(shù)來(lái)進(jìn)行排序。

思路就是把2,3,1組合成一個(gè)字符串，然后在字符串中取字符的下標(biāo)來(lái)變相排序。

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函數(shù)來(lái)進(jìn)行排序。

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

方法5

? ?

可以構(gòu)造子查詢來(lái)進(jìn)行排序，

里面使用的還是case when,當(dāng)然還可以使用其他的邏輯

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函數(shù)來(lái)進(jìn)行排序。

這種思路看起來(lái)有些取巧，但是不失為一種可擴(kuò)展的方法

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

接下來(lái)我們做幾種略微復(fù)雜的方法

方法7

? ?

在這里使用Hash的方法來(lái)解決，在問(wèn)題中已經(jīng)給出了：

(4-level)%3

我嘗試了如下的方式，對(duì)于(x+N)%5,對(duì)于x為（1,2，3）可以得到如下的表格?？梢钥吹巾樞蚨际前凑?12012這樣的頻率的，所以在這種模式下是得不到所需的結(jié)果的。

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當(dāng)做手環(huán)上面的3個(gè)數(shù)，既然它們可以正向旋轉(zhuǎn)，也可以反向旋轉(zhuǎn)，所謂的反向旋轉(zhuǎn)，輸出的頻率即為：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

觀察列表，我們可以看到對(duì)于4-x,7-x,10-x都是按照021的順序來(lái)排列的。

所以我們可以使用4-x,7-x,10-x得到如下的同樣結(jié)果：

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

? ?

我們可以得到一個(gè)大膽的結(jié)果，那就是對(duì)于3*N+1，這種方式都是可行的。

先輸出level和id的結(jié)果：

mysql> select (4-level)%3,level,id fromdept; +-------------+-------+----+ | (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 | +-------------+-------+----+

按照我們剛總結(jié)的方式，我們可以得到3N+1的模式都可以輸出結(jié)果，我們給出一個(gè)自負(fù)的測(cè)試結(jié)果，那就是對(duì)于任何大于0的數(shù)，測(cè)試結(jié)果冪等，所以我們構(gòu)造了一個(gè)隨機(jī)數(shù)，當(dāng)然這種方式需要注意的是，還需要對(duì)id進(jìn)行升序排列，否則輸出結(jié)果還是有些差異。

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

? ?

我們可以根據(jù)函數(shù)的思想來(lái)進(jìn)行分析，我們?cè)O(shè)想得到一個(gè)圖形。

對(duì)于1,2,3分別對(duì)應(yīng)f(1)<f(2)>f(3)，這顯然是一個(gè)絕對(duì)值函數(shù)。

如果我們想得到一個(gè)精確的值，可以設(shè)置函數(shù)為y=a|x-2|+bx+c

帶入1,2,3我們會(huì)得到如下的三個(gè)等式：

• 2b+c=3

• a+b+c=1

• a+3b+c=2

驗(yàn)算得到，a=-3/2, b=1/2, c=2

我們可以使用如下的三個(gè)函數(shù)來(lái)進(jìn)行驗(yàn)證。

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)

所以這個(gè)SQL就呼之欲出了。

select * fromdeptorder by -3*abs(level-2)/2+(level)/2+2 desc;

方法10

? ?

方法8的演進(jìn)版本，我們可以考慮擬合，即不用精確值。

我們可以這樣設(shè)想如果x=2為中位值，那么f(1)=f(3),

在f(1)<f(2)>f(3)的情況下，f(1)到f(2)單調(diào)遞增，而f(2)>f(3)使得單調(diào)遞減。

這個(gè)圖形我們不需要關(guān)注它的函數(shù)值到底是多少，而是著重于考慮中位值，而要單調(diào)遞減，則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)

? ?

有道無(wú)術(shù)，術(shù)可成；有術(shù)無(wú)道，止于術(shù)

歡迎大家關(guān)注Java之道公眾號(hào)

好文章，我在看??

總結(jié)

以上是生活随笔為你收集整理的一条看似不合理SQL和10个合理的解释的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。