查找所有共同作者 - 对许多映射表进行分组/分组

问题描述

  Table：Books 
 ------------- 
 ID名称
 1 Book1 
 2 Book2 
 3 Book3 
 4 Book4 
 
表：作者
 ------------- 
 ID名称
 1作者1 
 2作者2 
 3作者3 
 4作者4 
 
 
表：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 
  

所以，

• Book1由Author1，Author2，Author3编写


• Book2由Author2 ，Author3


• Book3仅由Author3写入


• Book4仅由Author4写入

问题是：
鉴于AuthorId，我需要找出其他作者与他共同创作的书籍数量：

  Give n AuthorId：1 
 ------------------- 
 AuthorId计数
 2 1 // 2仅合作编写book1 
 3 1 // 3只与book1共同创作
 1 1 //它可以，如果我在结果中得到author1 
 
给出AuthorId：2 
 --- ---------------- 
 AuthorId计数
 1 1 // 1与共同创作的book1 
 3 2 // 3共同创作book1和book2 
 2 2 //它可以，如果我在结果中获得作者2 
 
给定AuthorId：3 
 ------------- ------ 
 AuthorId计数
 1 1 // 1与其他人合作创作book1 
 2 2 // 2合作创作book1和2 
 3 3 / /它可以，如果我得到作者3的结果。 
 
给定AuthorId：4 
 ------------------- 
 AuthorId计数
 4 1 // I不应该为author1 0 author2 0，author3 0 for this 
  

- 编辑 -
我有一个解决方案，如下所示：从映射连接中选择aId，count（mapping.bId）（从映射中选择bId，其中aId =？）as tmp on mapping.bId = tmp.bId group by aId; code>

另外，@Giorgos Betsos在回复中提到了相同的内容。

我很好奇没有内部查询是否有可能。

解决方案

  SELECTAuthorId，COUNT（*）
 FROM BookAuthorMapping 
 WHERE BookIdIN（SELECTBookIdFROM BookAuthorMapping WHEREAuthorId= 1）
 GROUP BYAuthorId
  

您也可以使用 INNER JOIN ：

  SELECT t1。AuthorId，COUNT（*）
 FROM BookAuthorMapping AS t1 
 INNER JOIN BookAuthorMapping AS t2 ON t1。BookId= t2。BookIdAND t2。 AuthorId= 1 
 GROUP BY t1。AuthorId
  

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.

解决方案

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

这篇关于查找所有共同作者 - 对许多映射表进行分组/分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！