MySQL ORDER BY FIELD与% [英] MySQL ORDER BY FIELD with %
问题描述
我正在尝试使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.
这篇关于MySQL ORDER BY FIELD与%的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!