There are tables:

books -id -title -pubDate

authors -id -name

publish -id -name

book_author -book_id -author_id

book_publish -book_id -publish_id

You must select a list of the most productive (number of books per year), authors for each publisher. Help me please! 3rd day I can not think of.

UPD1:

This is what I was able to give birth to:

 SELECT a.firstName, a.middleName,a.lastName, b.title, p.name, COUNT(b.id) as cnt FROM authors as a, books as b, author_book as ab, publishing as p WHERE p.id = 3 AND ab.book_id = b.id AND b.pubDate > '2015-01-01 00:00:00' GROUP BY b.id HAVING cnt > 5

UPD2:

Work Request:

 SELECT a.firstName, p.name, COUNT(ab.author_id) AS total FROM books AS b JOIN publish_book AS pb ON pb.book = b.uid JOIN author_book AS ab ON b.uid = ab.book_id JOIN authors AS a ON ab.author_id = a.uid JOIN publishing as p ON p.uid = pb.publish WHERE p.id = 4 AND YEAR(b.pubDate) = 2016 GROUP BY ab.author_id ORDER BY total DESC LIMIT 10
  • What were you trying to do? What did you fail? Submit the request you wrote to - tCode 6:39 pm
  • @tCode, I probably should have written - "Write for me." Because I do not have at least somehow more or less working options. - Maxim Vlasov

2 answers 2

You can start from the next query, which lists the authors who worked for the publisher (with ID 3) in 2016, in descending order of the number of books published.

 SELECT a.name, COUNT(ba.author_id) AS total FROM books AS b JOIN book_publish AS bp ON b.id = bp.book_id JOIN book_author AS ba ON b.id = ba.book_id JOIN authors AS a ON ba.author_id = a.id WHERE bp.publish_id = 3 AND YEAR(b.pubDate) = 2016 GROUP BY ba.author_id ORDER BY total DESC

The result looks like this

 +--------------+-------+ | name | total | +--------------+-------+ | Автор 2 | 3 | | Автор 3 | 3 | | Автор 4 | 2 | | Автор 5 | 2 | | Автор 1 | 1 | +--------------+-------+ 
     SELECT TOP 1 p.name AS publish , a.name AS autor , COUNT(a.name) AS cnt FROM book_publish AS bp INNER JOIN publish AS p ON bp.publish_id = p.id INNER JOIN books AS b ON bp.book_id = b.id INNER JOIN book_autors AS ba ON b.id = ba.book_id INNER JOIN autors AS a ON ba.author_id = a.id WHERE YEAR(b.pubDate) = 2016 GROUP BY p.name , a.name ORDER BY cnt DESC

    True, there will be one problem if there are several authors with the maximum number of books for this publisher, only one of them will be derived (and there is no guarantee that it will be the same author each time). Or:

     SELECT publish , autor FROM ( SELECT p.name AS publish , a.name AS autor , COUNT(a.name) AS cnt FROM book_publish AS bp INNER JOIN publish AS p ON bp.publish_id = p.id INNER JOIN books AS b ON bp.book_id = b.id INNER JOIN book_autors AS ba ON b.id = ba.book_id INNER JOIN autors AS a ON ba.author_id = a.id WHERE YEAR(b.pubDate) = 2016 GROUP BY p.name , a.name ) as tmp HAVING tmp.cnt = MAX(tmp.cnt)

    REFERENCES:

    1. SELECT id HAVING maximum count of id
    2. Aggregate function of the aggregate function