在用 SELECT 查询的时候,如果用到了 IN ,那么查询结果中的顺序并不是按照 IN 后面所给的顺序返回,而是按照默认的升序排列。如下:
01 | mysql> SELECT * FROM test WHERE id IN (343,123,32,10,6,981,651,98,129); |
02 | +-----+--------+ |
03 | | id | name | |
04 | +-----+--------+ |
05 | | 6 | URdhMl | |
06 | | 10 | Xl[hJq | |
07 | | 32 | u^]~%p | |
08 | | 98 | Uq`InZ | |
09 | | 123 | yv](Ff | |
10 | | 129 | Owx_mt | |
11 | | 343 | =P3w,m | |
12 | | 651 | zR!yD= | |
13 | | 981 | 5%$EuH | |
14 | +-----+--------+ |
15 | 9 rows in set (0.00 sec) |
而如果想要让查询结果按照 IN 里面给的顺序的话,这里有几种方法:
转自:@喵了个咪
一、使用 FIND_IN_SET 建立一个派序列:
01 | mysql> SELECT * FROM test WHERE id IN (343,123,32,10,6,981,651,98,129) ORDER BY FIND_IN_SET( id , '343,123,32,10,6,981,651,98,129' ); |
02 | +-----+--------+ |
03 | | id | name | |
04 | +-----+--------+ |
05 | | 343 | =P3w,m | |
06 | | 123 | yv](Ff | |
07 | | 32 | u^]~%p | |
08 | | 10 | Xl[hJq | |
09 | | 6 | URdhMl | |
10 | | 981 | 5%$EuH | |
11 | | 651 | zR!yD= | |
12 | | 98 | Uq`InZ | |
13 | | 129 | Owx_mt | |
14 | +-----+--------+ |
15 | 9 rows in set (0.00 sec) |
下面这样可以看到 FIND_IN_SET 的操作方式:也就是 FIND_IN_SET 这个函数返回一个 1-n 递增的字符串,而ORDER BY 这个字符串就相当于是按升序排列了,而这个 1-n 对应着给入的那些 id
01 | mysql> SELECT id ,name,FIND_IN_SET( id , '343,123,32,10,6,981,651,98,129' ) AS sort_order FROM test WHERE id IN (343,123,32,10,6,981,651,98,129) ORDER BY FIND_IN_SET( id , '343,123,32,10,6,981,651,98,129' ); |
02 | +-----+--------+------------+ |
03 | | id | name | sort_order | |
04 | +-----+--------+------------+ |
05 | | 343 | =P3w,m | 1 | |
06 | | 123 | yv](Ff | 2 | |
07 | | 32 | u^]~%p | 3 | |
08 | | 10 | Xl[hJq | 4 | |
09 | | 6 | URdhMl | 5 | |
10 | | 981 | 5%$EuH | 6 | |
11 | | 651 | zR!yD= | 7 | |
12 | | 98 | Uq`InZ | 8 | |
13 | | 129 | Owx_mt | 9 | |
14 | +-----+--------+------------+ |
15 | 9 rows in set (0.00 sec) |
二、自己构建一个顺序 id 表,左连接 IN 查询结果集:
01 | mysql> SELECT * FROM ( |
02 | -> SELECT 343 AS id UNION |
03 | -> SELECT 123 UNION |
04 | -> SELECT 32 UNION |
05 | -> SELECT 10 UNION |
06 | -> SELECT 6 UNION |
07 | -> SELECT 981 UNION |
08 | -> SELECT 651 UNION |
09 | -> SELECT 98 UNION |
10 | -> SELECT 129 |
11 | -> ) AS table1 |
12 | -> LEFT JOIN test table2 ON table1. id =table2. id |
13 | -> WHERE table2. id IN (343,123,32,10,6,981,651,98,129); |
14 | +-----+------+--------+ |
15 | | id | id | name | |
16 | +-----+------+--------+ |
17 | | 343 | 343 | =P3w,m | |
18 | | 123 | 123 | yv](Ff | |
19 | | 32 | 32 | u^]~%p | |
20 | | 10 | 10 | Xl[hJq | |
21 | | 6 | 6 | URdhMl | |
22 | | 981 | 981 | 5%$EuH | |
23 | | 651 | 651 | zR!yD= | |
24 | | 98 | 98 | Uq`InZ | |
25 | | 129 | 129 | Owx_mt | |
26 | +-----+------+--------+ |
27 | 9 rows in set (0.00 sec) |
三、使用 UNION :
01 | mysql> SELECT * FROM test WHERE id =343 UNION |
02 | -> SELECT * FROM test WHERE id =123 UNION |
03 | -> SELECT * FROM test WHERE id =32 UNION |
04 | -> SELECT * FROM test WHERE id =10 UNION |
05 | -> SELECT * FROM test WHERE id =6 UNION |
06 | -> SELECT * FROM test WHERE id =981 UNION |
07 | -> SELECT * FROM test WHERE id =651 UNION |
08 | -> SELECT * FROM test WHERE id =98 UNION |
09 | -> SELECT * FROM test WHERE id =129; |
10 | +-----+--------+ |
11 | | id | name | |
12 | +-----+--------+ |
13 | | 343 | =P3w,m | |
14 | | 123 | yv](Ff | |
15 | | 32 | u^]~%p | |
16 | | 10 | Xl[hJq | |
17 | | 6 | URdhMl | |
18 | | 981 | 5%$EuH | |
19 | | 651 | zR!yD= | |
20 | | 98 | Uq`InZ | |
21 | | 129 | Owx_mt | |
22 | +-----+--------+ |
23 | 9 rows in set (0.00 sec) |