MySQL ORDER BY FIELD与％

本文介绍了MySQL ORDER BY FIELD与％的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试使ORDER BY FIELD与通配符一起使用，但未成功:

I am trying to make an ORDER BY FIELD work with a wildcard, and have been unsuccessful:

SELECT positions.*, departments.dept_name, departments.dept_url, divisions.dept_name AS div_name FROM positions LEFT JOIN departments ON positions.colleague_dept_code = departments.colleague_code LEFT JOIN departments AS divisions ON positions.colleague_div_code = divisions.colleague_code WHERE colleague_id = '$colleague_id' ORDER BY FIELD(positions.colleague_position_id, 'A%', 'F%', 'T%', 'S%', 'C%')

colleague_position_id字段具有由我们的MIS系统生成的文本ID，我希望以A开头的位置显示第一位，以F开头的位置显示第二位，依此类推，等等.

The colleague_position_id field has a text ID generated by our MIS system, and I'd like for positions starting with A to display first, F to display second, etc., etc.

我们将不胜感激.

谢谢！

推荐答案

这应该使您对其有最大的控制权:

This should give you the most control over it:

order by case left(positions.colleague_position_id, 1) when 'A' then 1 when 'F' then 2 when 'T' then 3 when 'S' then 4 when 'C' then 5 else 6 end, positions.colleague_position_id

这是因为您可以将所有不匹配的值发送到所需的位置(在本例中为末尾). field()函数将为不匹配的值返回0，并将它们放在结果集的顶部，甚至比以A开头的结果更早.

This is because you can send all non-matching values to the position you want (in this case at the end). The field() function will return 0 for non matching values and will put them at the top of the result set even before the ones starting with A.

此外，您也可以像在示例中一样按positions.colleague_position_id进行排序，因此对于许多以相同字母开头的positions.colleague_position_id，它们仍将保持顺序.

Additionally, you can also order by positions.colleague_position_id as I did in the example, so that for many positions.colleague_position_id that start with the same letter they will still be in order.