MySQL对IN()中的参数进行排序 [英] MySQL sort after argument in IN()

问题描述

我有一个包含很多与我的数据库相对应的ID的字符串. 喜欢:

I have string containing a lot of IDs corresponding to my database. Like:

1,2,3,4,5

然后我执行mySQL查询以选择所有这些行:

I then do a mySQL query to select all those rows:

SELECT * FROM `table` WHERE `id` IN (".$myIDs.")

我希望mySQL按照我选择的IN()的顺序返回行.

I want mySQL to return the rows in the order they are in my IN() select.

所以，如果我代替了

2,1,3,4,5

在使用PHP进行抓取时，我将第2行作为第一行. 没有任何ORDER BY的情况下，它似乎首先返回了最低的ID.

I would have #2 as first row, when fetching in PHP. Without any ORDER BY it looks like it return the lowest ID first.

让我知道您是否需要进一步的指摘.

Let me know if you need further exlpanation.

推荐答案

您应该可以通过

You should be able to do this via the FIELD() function like so:

SELECT * FROM `table` WHERE `id` IN (2,1,3,4,5) ORDER BY FIELD(`id`, 2,1,3,4,5) DESC

也就是说:

SELECT
  *
FROM
  `table`
WHERE
  `id` IN (".$myIDs.")
ORDER BY
  FIELD(`id`, ".$myIDs.") DESC

此博客文章中的更多信息: 使用列值对MySQL行进行排序 .

More in this blog post: Sorting MySQL rows using column values.

这篇关于MySQL对IN()中的参数进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！