看似简单的MYSQL查询

发布于 2024-12-09 22:01:08 字数 508 浏览 0 评论 0原文

我的表格如下所示：

CREATE TABLE Author(
authorID INT PRIMARY KEY,
name VARCHAR(30)
);

CREATE TABLE book(
ISBN INT PRIMARY KEY,
title VARCHAR(30),
authorID INT,
inventory INT,
paperBack BOOLEAN,
fiction BOOLEAN,
FOREIGN KEY (authorID) REFERENCES Author(authorID)
);

我需要找出哪位作者写的书最多。
我正在混合使用以下内容。我想我在将它们拼凑在一起时遇到了麻烦...

  SELECT authorID, count(*) 
    from book 
group by authorID;

我不知道如何获取计数最高的单行，然后只获取该行的authorID。一旦我有了authorID，我就知道如何获取名字了。

原文

Here is what my tables look like:

CREATE TABLE Author(
authorID INT PRIMARY KEY,
name VARCHAR(30)
);

CREATE TABLE book(
ISBN INT PRIMARY KEY,
title VARCHAR(30),
authorID INT,
inventory INT,
paperBack BOOLEAN,
fiction BOOLEAN,
FOREIGN KEY (authorID) REFERENCES Author(authorID)
);

I need to find out which author has written the most books.
I am working with a mixture of the following. I guess I am having trouble piecing it all together...

  SELECT authorID, count(*) 
    from book 
group by authorID;

I am not sure how to get the single row that has the highest count and then only get the authorID of that row. Once I have that authorID, I know how to get the name.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

信愁 2024-12-16 22:01:08

试试这个：

select a.name,count(*)
from author a
join book b on b.authorID=a.authorID
group by a.Name
order by 2 desc
limit 1

Try this:

select a.name,count(*)
from author a
join book b on b.authorID=a.authorID
group by a.Name
order by 2 desc
limit 1

回复收藏 0 原文

无人问我粥可暖 2024-12-16 22:01:08

如果您想要的只是作者的 ID，请使用 ORDER BY 和 LIMIT 1 正如其他人指出的那样。但是，如果您想要来自作者的其他字段，或者如果您对多个作者感兴趣（第二高计数、最低计数等），那么您应该考虑联接到派生表，像这样：

SELECT Author.*, d1.book_count
FROM Author
  JOIN (SELECT authorID, count(authorID) as book_count FROM book GROUP BY authorID) d1
  ON Author.authorID = d1.authorID
ORDER BY 
  d1.book_count

会给出这样的结果集：

 +----------------------------------+
 | AuthorID  |  Name  |  book_count |
 +----------------------------------+
 | 1         | bob    | 20          |
 | 9001      | sam    | 18          |

 ...

If all you want is the ID of the author, then use ORDER BY and LIMIT 1 as others have pointed out. However, if you're going to want other fields from Author, or if you are interested in multiple authors (second highest count, lowest count, etc), then you should consider joining to a derived table, like this:

SELECT Author.*, d1.book_count
FROM Author
  JOIN (SELECT authorID, count(authorID) as book_count FROM book GROUP BY authorID) d1
  ON Author.authorID = d1.authorID
ORDER BY 
  d1.book_count

Would give a result set like this:

 +----------------------------------+
 | AuthorID  |  Name  |  book_count |
 +----------------------------------+
 | 1         | bob    | 20          |
 | 9001      | sam    | 18          |

 ...

回复收藏 0 原文