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 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)

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

谢谢。

- dev-null

Answer 1

尝试用 title 替换掉 distinct title，这样就可以解决你的问题了。

- krishnang

---

不行，那也不行。 - dev-null

Answer 2

我会分三步解决这个问题。首先获取每个城市每本书的数量计数。

select customers.city, books.title, count(books.title) as count
from loaned, books, customers
where loaned.userID = customers.userID
and loaned.bookID = books.bookID
group by customers.city, books.title

此查询将返回以下行。

+------------+-------------------------------+-------+
| city       | title                         | count |
+------------+-------------------------------+-------+
| Harrogate  | The cross rabbit              | 1     |
| Harrogate  | PHP and MySQL web development | 2     |
| Whitehaven | Greek Mythology               | 1     |
| Whitehaven | Dino-soaring                  | 2     |
| Sale       | Magic tricks                  | 3     |
| Sale       | Dino-soaring                  | 2     |
+------------+-------------------------------+-------+

利用这些数据，我将对每个城市进行分组，并找出数量最多的城市。

select city, max(count) as count
from 
(
  select customers.city , books.title, count(books.title) as count
  from loaned, books, customers
  where loaned.userID = customers.userID
  and loaned.bookID = books.bookID
  group by customers.city, books.title
) as city_book_max_count
group by city

这将返回这些行，

+------------+-------+
| city       | count |
+------------+-------+
| Harrogate  | 2     |
| Whitehaven | 2     |
| Sale       | 3     |
+------------+-------+

利用这两个表格的数据，我们可以按城市和数量将它们连接起来，以获取在两个表格中都匹配的相应书籍。

select city_book_count.city, city_book_count.title
from 
(
  select customers.city , books.title, count(books.title) as count
  from loaned, books, customers
  where loaned.userID = customers.userID
  and loaned.bookID = books.bookID
  group by customers.city, books.title
) as city_book_count
join
(
  select city, max(count) as count
  from 
  (
    select customers.city , books.title, count(books.title) as count
    from loaned, books, customers
    where loaned.userID = customers.userID
    and loaned.bookID = books.bookID
    group by customers.city, books.title
  ) as city_book_count_temp
  group by city
) as city_book_max_count
on city_book_count.city = city_book_max_count.city
  and city_book_count.count = city_book_max_count.count

- ace

Answer 3

感谢所有回答的人。由于这是一个测试问题，我不想“剪切和粘贴”别人的工作，而是使用他们的逻辑来制作自己的查询。以下是我的查询结果：

select city, title
from (
    select customers.city as city, books.title as title, count(books.title) as cnt
    from books, customers, loaned
    where loaned.userID = customers.userID
    and loaned.bookID = books.bookID
    group by title, city
    order by cnt desc) as tbl
group by city

结果：

+------------+-------------------------------+
| city       | title                         |
+------------+-------------------------------+
| Harrogate  | PHP 和 MySQL 网站开发         |
| Sale       | 魔术技巧                      |
| Whitehaven | 恐龙飞翔                      |
+------------+-------------------------------+
共 3 行 (0.00 秒)

- dev-null

Answer 4

我正在删除客户表，因为这个表没有任何输出

select customers.city , books.title , count(books.title) Total                       
from loaned, books, customers                                                                         
where loaned.userID = customers.userID                                                       
and loaned.bookID = books.bookID 
    group by customers.city , books.title order by 3 desc

- Vinit Prajapati

---

是的，有的，那是我从客户处获取城市信息的地方。这不存储在借贷或图书表中。 - dev-null

谢谢，这个方法可以工作，但是现在它有了第三列（显示标题数量），并且还显示了每种其他标题的数量。有没有办法只显示每个城市最受欢迎的书籍？ - dev-null