MySQL-IN()中的ORDER BY值 [英] MySQL - ORDER BY values within IN()

问题描述

我希望按照它们在IN()函数中输入的顺序对在以下查询中返回的项目进行排序.

I'm hoping to sort the items returned in the following query by the order they're entered into the IN() function.

输入:

SELECT id, name FROM mytable WHERE name IN ('B', 'A', 'D', 'E', 'C');

输出:

|   id   |   name  |
^--------^---------^
|   5    |   B     |
|   6    |   B     |
|   1    |   D     |
|   15   |   E     |
|   17   |   E     |
|   9    |   C     |
|   18   |   C     |

有什么想法吗?

推荐答案

SELECT id, name
FROM mytable
WHERE name IN ('B', 'A', 'D', 'E', 'C')
ORDER BY FIELD(name, 'B', 'A', 'D', 'E', 'C')

FIELD 函数返回位置其余字符串列表中的第一个字符串.

The FIELD function returns the position of the first string in the remaining list of strings.

但是，从性能角度考虑，最好有一个索引列来表示您的排序顺序，然后再按此列进行排序.

However, it is much better performance-wise to have an indexed column that represents your sort order, and then sort by this column.

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