如何让MySQL按子查询结果或指定列表顺序返回查询行？

当然可以搞定这个问题！MySQL默认会按照表的主键、索引或者存储引擎的默认规则返回结果，所以直接用IN子句时，不会自动保持你指定的列表顺序。不过有几种简单的办法能实现你想要的效果：

方法1：使用FIELD()函数（最常用） #

FIELD()函数会返回第一个参数在后续参数列表中的位置索引，我们可以利用它来指定排序规则：

SELECT * FROM $table 
WHERE Id IN (8, 4, 1, 3)
ORDER BY FIELD(Id, 8, 4, 1, 3);

举个例子，当Id=8时，FIELD(Id,8,4,1,3)返回1；Id=4时返回2，以此类推。通过ORDER BY这个返回值，结果就会严格按照你传入的(8,4,1,3)顺序排列。

方法2：使用CASE WHEN语句（更灵活） #

如果需要更复杂的排序逻辑（比如给不同的ID设置特殊排序优先级），可以用CASE WHEN来自定义排序值：

SELECT * FROM $table 
WHERE Id IN (8, 4, 1, 3)
ORDER BY 
    CASE Id 
        WHEN 8 THEN 1
        WHEN 4 THEN 2
        WHEN 1 THEN 3
        WHEN 3 THEN 4
        ELSE 5  -- 其他不在列表中的值排最后
    END;

这种方法的优势是可以灵活调整每个ID对应的排序优先级，适合定制化需求。

注意事项 #

• 确保Id字段的数据类型和你传入的数值类型完全匹配，否则FIELD()或CASE WHEN可能无法正确匹配排序
• 如果你的ID列表非常长（比如包含上千个值），上述两种方法可能会导致查询性能下降。这时可以考虑创建临时表，将ID和对应的排序序号插入临时表，再通过关联查询来排序：

  -- 创建临时表并插入ID和排序值
  CREATE TEMPORARY TABLE temp_order (id INT, sort_order INT);
  INSERT INTO temp_order VALUES (8,1), (4,2), (1,3), (3,4);
  
  -- 关联查询排序
  SELECT t.* FROM $table t
  JOIN temp_order to ON t.Id = to.id
  WHERE t.Id IN (8,4,1,3)
  ORDER BY to.sort_order;
  

内容的提问来源于stack exchange，提问作者Jay