我有一个查询语句,输出结果如下:
谢谢。
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 city order 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)这似乎是按字母顺序排序,而不是按受欢迎程度排序。 得到数据后,我认为将其按照所需的方式排序很容易,但事实并非如此。 我需要执行某种联接或比这更复杂的操作吗?
谢谢。