MySQL命令按特定字符串 [英] Mysql Order By specific string

问题描述

我似乎找不到答案:

说我有一个像这样的表:

Say I have a table like so:

ID   Name
------------------------
1    AAAAAAAAA
2    ABAAAAAAA
3    BBAAAAAAA
4    CDAAAAAAA
5    BBAAAAAAA

有没有一种方法可以按name排序-但是-从说BB开始该命令，然后让它循环返回(而不是从AZ，从BB到BA)

Is there a way I can order by name - but - start that order from say BB and let it loop back round (instead of from A-Z, go from BB to BA)

最终结果将是:

3    BBAAAAAAA
5    BBAAAAAAA
4    CDAAAAAAA
1    AAAAAAAAA
2    ABAAAAAAA

这有意义吗?

推荐答案

如果希望BB出现在开头，则可以使用:

If you want the BB to appear at the beginning you can use:

select *
from yourtable
order by case when substring(name, 1, 2) = 'BB' then 0 else 1 end

请参见带演示的SQL提琴

如果您想让CD出现在第二位，请使用:

If you want CD to appear second, then use:

select *
from yourtable
order by 
  case 
    when substring(name, 1, 2) = 'BB' then 0 
    when substring(name, 1, 2) = 'CD' then 1 
    else 2 end, name

请参见带有演示的SQL提琴

第二个查询的结果

| ID |      NAME |
------------------
|  3 | BBAAAAAAA |
|  5 | BBAAAAAAA |
|  4 | CDAAAAAAA |
|  1 | AAAAAAAAA |
|  2 | ABAAAAAAA |

这篇关于MySQL命令按特定字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！