MySQL 按不同计数排序

Question

我有一个生成以下内容的选择查询:

select customers.city , books.title                                                                   from loaned, books, customers                                                                         where loaned.userID = customers.userID                                                                and loaned.bookID = books.bookID +------------+-------------------------------+| city       | title                         |+------------+-------------------------------+| Harrogate  | The cross rabbit              || Harrogate  | PHP and MySQL web development || Harrogate  | PHP and MySQL web development || Whitehaven | Greek Mythology               || Whitehaven | Dino-soaring                  || Whitehaven | Dino-soaring                  || Sale       | Magic tricks                  || Sale       | Magic tricks                  || Sale       | Magic tricks                  || Sale       | Dino-soaring                  || Sale       | Dino-soaring                  |+------------+-------------------------------+11 rows in set (0.00 sec)

我想找到每个城市最受欢迎的标题，所以我做了以下事情:

group by cityorder by count(distinct title) desc

但是这不会产生正确的结果。我得到:

+------------+-------------------------------+| city       | title                         |+------------+-------------------------------+| Sale       | Dino-soaring                  || Whitehaven | Dino-soaring                  || Harrogate  | PHP and MySQL web development |+------------+-------------------------------+3 rows in set (0.00 sec)

这似乎是按字母顺序排序的，而不是按受欢迎程度排序的。获得数据后，我认为很容易按照我的要求对其进行排序，但事实并非如此。我是否需要执行某种联接或比这更复杂的操作？

提前致谢。

Answer 1

尝试将 distinct title 替换为 title，这应该可以解决您的问题..