按特定字段值优先排序的MySql不起作用

Question

你好，我有一个新闻页面，我想显示用户的城市新闻在顶部。例如，这些是按时间顺序下降的新闻。

+----------+-----------+-----------------------+
| CityCode | entrytime |      newsheader       |
+----------+-----------+-----------------------+
|       11 |      3800 | great opening         |
|       10 |      3700 | flood alert           |
|       12 |      3600 | new mall              |
|       13 |      3500 | pollution at the city |
|       13 |      3400 | new mayor             |
|       12 |      3300 | house fire            |
|       11 |      3200 | traffic accident      |
|       10 |      3000 | Festival at city      |
+----------+-----------+-----------------------+

我住在城市12号，我想看到CityCode=12站在顶端，然后是其他类似的新闻。

+----------+-----------+-----------------------+
| CityCode | entrytime |      newsheader       |
+----------+-----------+-----------------------+
|       12 |      3600 | new mall              |
|       12 |      3300 | house fire            |
|       11 |      3800 | great opening         |
|       10 |      3700 | flood alert           |
|       13 |      3500 | pollution at the city |
|       13 |      3400 | new mayor             |
|       11 |      3200 | traffic accident      |
|       10 |      3000 | Festival at city      |
+----------+-----------+-----------------------+

我试过了

Select * from news order by FIELD(CityCode,12),entrytime desc

和

Select * from news order by CityCode=12,entrytime desc 

这些密码不管用。我使用MySQL5.5如何解决这个问题？谢谢

Answer 1

尝试如下:在order子句中为字段添加desc (城市代码，12)

Select * from news order by FIELD(CityCode,12) desc,entrytime desc

检查字段函数的局限性

利用字段函数进行排序

Answer 2

请尝试以下查询：

SELECT *
FROM NEWS
ORDER BY CASE WHEN CityCode=12 THEN 1 ELSE 2 END;

这是小提琴-

http://sqlfiddle.com/#!2/f5dde/1