MySQL - IN() 中的 ORDER BY 值

问题描述

我希望按照在 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 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！