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