我有一个选择查询,产生以下内容:
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)
这似乎是按字母顺序排序,而不是按人气排序.
获得数据后,我认为按照我的要求订购数据很容易,但事实并非如此.
我是否需要进行某种加入或更复杂的事情?
提前致谢.