我可以按IN值订购吗？

Can I sort by the value of an IN query?

我可以按IN查询​​的值排序吗?

The following defaults to "order item_id lowest first" but i actually want the sort as entered... is this possible?

以下默认为“order item_id first first first”但我实际上希望排序为输入...这可能吗?

e.g.

select item_id, item_title, item_source from items where item_id IN ('1676','1559','1672')

I want to return:

我想回来:

item_id     item_title    item_source
-------     ----------    -----------
1676        item_a        source_a
1559        item_f        source_f
1672        item_c        source_c

3 个解决方案

#1

You might consider using the FIND_IN_SET or FIELD functions to organize your results, if you already know the IDs beforehand.

如果您事先已经知道了ID,则可以考虑使用FIND_IN_SET或FIELD函数来组织结果。

SELECT 
    item_id,
    item_title,
    item_source
FROM items
WHERE item_id IN ('1676','1559','1672')
ORDER BY FIELD(item_id, '1676', '1559', '1672')

or

ORDER BY FIND_IN_SET(item_id, '1676,1559,1672')

The drawback, of course, is that you're specifying the IDs twice. I'm thinking pretty much any reasonably performing solution will do so, though. The only way to get around that would be to have a sort order field or something like that.

当然,缺点是你要两次指定ID。不过,我认为任何合理执行的解决方案都会这样做。解决这个问题的唯一方法是使用排序顺序字段或类似的东西。

#2

You can JOIN these values in the IN predicate like so:

您可以在IN谓词中加入这些值,如下所示:

SELECT 
  i.item_id,
  i.item_title
  i.item_source
FROM items i
INNER JOIN
(
   SELECT 0 sortid, 1676 id
   UNION ALL
   SELECT 1,        1559 
   UNION ALL
   SELECT 2,        1672
) t ON i.item_id = t.id
ORDER BY t.sortid

SQL Fiddle Demo

#3

You would need a custom ORDER BY CASE to apply a sort rank to each of the possible values. Obviously this becomes difficult as the size of the set increases.

您需要一个自定义ORDER BY CASE来对每个可能的值应用排序等级。显然,随着集合的大小增加,这变得困难。

select
  item_id, 
  item_title, 
  item_source 
from items
where item_id IN ('1676','1559','1672')
/* Custom ORDER BY applies lower order numbers to the lowest value, etc... */
ORDER BY 
  CASE 
    WHEN item_id = 1676 THEN 0
    WHEN item_id = 1559 THEN 1
    WHEN item_id = 1672 THEN 2
    /* If you had others (which you don't because they're limited by the IN() clause, include an ELSE */
    ELSE 99
  END,
  /* Then complete the ORDER BY with other cols to order on as needed... */
  item_source