sql - 在 SQL 中，如何从包含图书馆表和图书表的数据库中创建包含图书数量的图书馆列表？

Question

在最近的一次编程面试中，我被问到一个 SQL 问题，我给出了一个我认为合理的答案，但我的回答引起了 dba 的强烈反对，而且我无法弄清楚原因。

从那以后，我对这个问题进行了更多的思考，我无法弄清楚我的答案有什么可怕的，所以我在这里寻求启发，以找出正确的方法，或者找不到更好的方法从包含图书馆表和图书表的数据库生成图书馆报告和图书数量。

请注意，我对场景做了一些改动，因此措辞与面试问题不同，但任务是相同的。

这是该问题的最小架构:

create table library (
  id integer primary key,
  name char(8)
);

create table book (
  id integer primary key,
  name char(8),
  library_id integer,
  foreign key (library_id) references library(id)
);

任务是列出图书馆的名称以及拥有两本书或更多书籍的图书馆中的书籍数量。

而且，这是我提出的解决方案:

select
  a.name as name,
  b.nbooks as nbooks
from
    library as a,
    (
        select
            min(library_id) as library,
            count(id) as nbooks
        from
            book
        group by 
            library_id
    ) as b
where
    ( nbooks > 1 ) and (a.id = b.library)
;

再三考虑，使用显式 inner join 可能会更好。除此之外，您能否向我指出潜在的陷阱(一般的或与特定数据库相关的)以及生成此报告的正确方法？

Answer 1

这是一个简单的方法:

select l.name, count(*) as numbooks
from library l join
     books b
     on l.id = b.library_id
group by l.name
having count(*) > 1

您的回答在技术上是可以的。 DBA 可能不关心其他人可能关心的某些风格化的东西(例如使用“a”作为库的别名而不是“l”)。子查询是不必要的，min(library_id) 是不必要的。您可以将聚合函数应用于按列分组，但通常不会这样做。

DBA 可能会回应的最大问题是在 WHERE 子句中而不是在 ON 子句中加入连接条件。这是危险的，因为如果您将其遗漏或进行看似无害的修改，查询可能会变成 CROSS JOIN 而不是 INNER JOIN。