mysql 按特定字符串查询,MySQL命令按特定字符串

I can't seem to find an answer to this one:

Say I have a table like so:

ID Name

------------------------

1 AAAAAAAAA

2 ABAAAAAAA

3 BBAAAAAAA

4 CDAAAAAAA

5 BBAAAAAAA

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)

The final result would then be:

3 BBAAAAAAA

5 BBAAAAAAA

4 CDAAAAAAA

1 AAAAAAAAA

2 ABAAAAAAA

Does that make sense?

解决方案

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

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

Result for second query:

| ID | NAME |

------------------

| 3 | BBAAAAAAA |

| 5 | BBAAAAAAA |

| 4 | CDAAAAAAA |

| 1 | AAAAAAAAA |

| 2 | ABAAAAAAA |