有时候我们需要按照in条件里的id顺序输出结果,可sql语句在不加order by的时候是按照asc排序的,下边的sql解决按照in条件顺序的id输出查询结果
mysql写法:
SELECT * FROM EVENT WHERE eventId IN(443,419,431,440,420,414,509) ORDER BY INSTR(',443,419,431,440,420,414,509,',CONCAT(',',eventId,','))
oracle写法:
select name from order where oderid in(111,222,333,444,555,666)order by instr('111,222,333,444,555,666',orderid)
sqlserver写法:
Select * From Product Where id in (1,1588,15782,9887,54) Order By charindex(','+ id +',', ',1,1588,15782,9887,54,')
---------------------------------------------------------------------------------------------------------------------------------------------
作者: straul 日期: 2011 年 12 月 16 日
在用 SELECT 查询的时候,如果用到了 IN ,那么查询结果中的顺序并不是按照 IN 后面所给的顺序返回,而是按照默认的升序排列。如下:
01 | mysql> SELECT * FROM test WHERE id IN (343,123,32,10,6,981,651,98,129); |
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' ); |
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 | +-----+--------+------------+ |
14 | +-----+--------+------------+ |
15 | 9 rows in set (0.00 sec) |
二、自己构建一个顺序 id 表,左连接 IN 查询结果集:
02 | -> SELECT 343 AS id UNION |
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 | +-----+------+--------+ |
16 | +-----+------+--------+ |
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; |
23 | 9 rows in set (0.00 sec) |