Mysql select 4 tables with 3 count

Question

我有四张 table

usersID  display_name1   Name12   Name23   Name3

A user can add books to table books

booksbook_id AddedByuserID1       12       13       24       3

Also a user can add ebooks to table ebooks

ebooksebook_id AddedByuserID1       12       23       24       3

Now a user can add books only to his collection (not ebooks)

collectionuserID  book_id1       11       21       3

I need an output like this:

display_name    books_added ebooks_added    books_in_collectionName1           2           1               3Name2           1           2               2Name3           1           1               1

This is what got:

SELECT users.*, COUNT(DISTINCT collection.book_id) AS books_in_collection, COUNT(DISTINCT books.AddedByuserID) AS books_added, COUNT(DISTINCT ebooks.AddedByuserID) AS ebooks_added
FROM users LEFT JOIN collection ON users.ID = collection.userID
LEFT JOIN books ON users.ID = books.AddedByuserID
LEFT JOIN ebooks ON users.ID = ebooks.AddedByuserID
GROUP BY users.ID
ORDER BY display_name ASC

用户 display_name 和收集计数显示正确，但其他两个计数显示为 1。如果我删除 DISTINCT，集合计数显示为 86，而其他两个计数则什么也没有显示。

我试图理解 LEFT join 并阅读教程，但我现在卡住了。

Answer 1

我会建议相关子查询:

select u.*,
       (select count(*) from collection c where u.id = c.userId) as books_in_collection,
       (select count(*) from books b where u.id = b.AddedByUserId) as books_added,
       (select count(*) from ebooks e where u.id = e.AddedByUserId) as ebooks_added
from users u;

不过，我不知道您为什么要过滤掉第三个用户。