SQL select语句来识别"Bob"写过的每本书的合著者

Question

架构：

BID VARCHAR(40) NOT NULL,
AID VARCHAR(40) NOT NULL,
CONSTRAINT PK PRIMARY KEY (BID, AID)

实例：

bid    | aid  
-------+--------
Book 1 | Ada  
Book 1 | Bob  
Book 1 | Carl  
Book 2 | Ada  
Book 2 | Bob  
Book 2 | Carl 
Book 2 | Dave  
Book 3 | Carl  

Answer 1

您需要标记正在使用的DB。但你可以试试这样的东西：

select aid from table where bid = (select bid from table where aid='Bob')

• 以Bob为作者获取图书id
• 获取这些图书id的所有作者。它也会返回Bob

Answer 2

您尝试过以下操作吗。

SELECT c.aid, c.bid
FROM table c,
(SELECT a.bid, b.aid 
FROM (SELECT bid, count(bid) as cbid 
FROM table
WHERE cbid > 1
GROUP BY bid) a 
INNER JOIN table b 
ON a.bid = b.bid
WHERE b.aid = 'Bob' ) d
ON c.bid = d.bid
WHERE c.aid <> 'Bob'

这将返回Bob的合著者和书籍

Answer 3

让我们来分解一下。

首先，鲍勃写了什么书

select distinct bid from books where aid = 'bob'

然后，谁与人合著了《》一书

select aid from books where aid <> 'bob' and bid in (select distinct bid from books where aid = 'bob')

那么，他们是否共同撰写了所有的书

select aid from books where aid <> 'bob' and bid in (select distinct bid from books where aid = 'bob') 
group by aid
having count(distinct bid) = (select count(distinct bid) from books where aid = 'bob')