Find all co authors - Faceting/Grouping for many t

问题:

Table : Books 
-------------
ID    Name
1     Book1
2     Book2
3     Book3
4     Book4

Table : Authors 
-------------
ID    Name
1     Author1
2     Author2
3     Author3
4     Author4


Table :  BookAuthorMapping
---------------------------
ID    BookId    AuthorId
1     1         1
2     1         2
3     1         3
4     2         2
5     2         3
6     3         3
7     4         4  

So,

• Book1 is written by Author1,Author2,Author3
• Book2 is written by Author2,Author3
• Book3 is only written by Author3
• Book4 is only written by Author4

The problem is : Given an AuthorId, I need to find out How many books did other Authors co author with him:

Example :

Given AuthorId: 1
-------------------
AuthorId   Count 
2          1           // 2 has co-authored only book1
3          1           // 3 has co-authored only book1
1          1           // Its okay, if i get author1 in the result

Given AuthorId: 2
-------------------
AuthorId   Count 
1          1           // 1 has co-authored only book1
3          2           // 3 has co-authored book1 and book2
2          2           // Its okay if i get author 2 in the result

Given AuthorId: 3
-------------------
AuthorId   Count 
1          1          // 1 has co-authored only book1
2          2          // 2 has co-authored book1 and 2
3          3          // Its okay if i get author 3 in the result.

Given AuthorId: 4
-------------------
AuthorId   Count 
4          1     // I should not get author1 0 author2 0 , author3 0 for this

-- EDIT -- I had a solution like : select aId, count(mapping.bId) from mapping join (select bId from mapping where aId = ?) as tmp on mapping.bId = tmp.bId group by aId;

Also, @Giorgos Betsos mentioned the same in the replies.

I'm curious if it is possible without the inner query.

回答1:

Try this:

SELECT "AuthorId", COUNT(*)
FROM BookAuthorMapping
WHERE "BookId" IN (SELECT "BookId" FROM BookAuthorMapping WHERE "AuthorId" = 1)
GROUP BY "AuthorId"

Demo here

You can alternatively use an INNER JOIN:

SELECT t1."AuthorId", COUNT(*)
FROM BookAuthorMapping AS t1
INNER JOIN BookAuthorMapping AS t2 ON t1."BookId" = t2."BookId" AND t2."AuthorId" = 1
GROUP BY t1."AuthorId"

Demo here

回答2:

You can also do this with window functions:

select authorid, count(*)
from (select bam.*,
             max(case when AuthorId = 1 then 1 else 0 end) over (partition by bookid) as HasAuthor1
      from BookAuthorMapping bam
     ) bam
where hasAuthor1 = 1
group by authorid;

Under some circumstances, the window function would be faster than an approach using join or in.