假设我有两个包含这些数据的表:
author
id | name
---------
1 | John
2 | Jack
3 | James
4 | Bob
5 | Gorge
book
id | author_id | title | state
-------------------------------------
1 | 1 | book_1 | published
2 | 1 | book_2 | pre-published
3 | 1 | book_3 | published
4 | 1 | book_4 | rejected
5 | 2 | book_5 | published
6 | 3 | book_6 | rejected
7 | 3 | book_7 | pre-published
8 | 4 | book_8 | rejected
我想要的是一个SQL查询,它可以给我每个作者的ID以及出版的图书的名称和编号,即使他们已经出版了0本.我认为这可能是一个简单的问题,但我坚持了下来,我想要的是:
id | name | num_of_published_books
------------------------------------
1 | John | 2
2 | Jack | 1
3 | James | 0
4 | Bob | 0
5 | Gorge | 0
我可以取到前两排的第John
和Jack
行.我还可以根据author_id
和state
对它们进行分组.但这不是我想要的.
请注意,我不想使用子查询.