返回写过两部以上小说的作者的id、名字和姓氏

Question

select aid, fname, lname 
from author 
where aid in (
    select aid
    from written_by
        join book on book.isbn = written_by.isbn
    group by aid
    having count(*) > 2
); 

编辑：想出来了。在group by之前添加了btype=“novel”，现在它可以正常工作。

Answer 1

有几种方法可以形成这个查询。您已经在()中使用了，这里有一个您应该知道的替代方案。

使用内部联接

select aid, fname as "First Name", lname as "Last Name"
from author a
inner join (
    select aid
    from written_by
        join book on book.isbn = written_by.isbn
    where btype = 'novel'
    group by aid
    having count(*) > 2
    ) g on a.aid = g.aid

注意：对于文字（如“novel”）使用单引号，对于标识（如包含空格的列别名）使用双引号。