如何在包含谁在 mysql 中写过大部分书的列表中找到作者

问题描述

我有两个表作家和博客.Writer 表有 id 和 name 列.博客表有 id、blog 和 writer_id.我必须找到写更多博客的作家.

I have two tables writer and blog. Writer table have id and name column. Blog table have id,blog and writer_id.I have to find writer who writes more blog.

推荐答案

我根据您非常简短的描述创建了一个示例架构，并根据该架构提供了一个答案.

I have created a sample schema based on your very brief description, and provide an answer based on that schema.

create table writer(id int,name text);
create table blog(id int,writer_id int,blog text);

insert into writer values
  (1,'Writer A'), (2,'Writer B'), (3,'Writer C'), (4,'Writer D'),
  (5,'Writer E'), (6,'Writer F'), (7,'Writer G');

insert into blog values
  (1,1,'Blog 1'), (2,1,'Blog 2'), (3,3,'Blog 3'), (4,3,'Blog 4'),
  (5,2,'Blog 5'), (6,3,'Blog 6'), (7,3,'Blog 7'), (8,6,'Blog 8'),
  (9,4,'Blog 9');

select w.name,count(b.id) no_of_books
  from writer w join blog b on w.id = b.writer_id
  group by w.id
  order by no_of_books desc
  limit 1;

通过更改 limit 部分，您可以将前 1 更改为前 2 或 3 等.

By changing the limit part you can change from top 1 to top 2 or 3 etc.

这篇关于如何在包含谁在 mysql 中写过大部分书的列表中找到作者的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！