sql - Postgres 查询返回只有一位作者的书籍

Question

我正在尝试进行查询，以返回书籍中的书名、书籍摘要和作者姓名，其中只有一位作者按书籍 ID 排序。一本书可以有很多作者，一个作者可以写很多书，所以这是多对多的关系，但我做错了什么或忘记了什么。

我的桌子是：

book_tb(id, title, summary);
author_tb(id, name);
book_author_tb(id, book_id, author_id);

我已经尝试过：

SELECT b.title, b.summary, a.name 
FROM book_tb b
INNER JOIN book_author_tb ba ON b.id = ba.book_id
INNER JOIN author_tb a ON ba.author_id = a.id
GROUP BY b.title, b.summary, a.name, b.id
HAVING count(ba.author_id) = 1
ORDER BY b.id;

标签： sqlpostgresql

Answer 1

一种方法是：

SELECT b.title, b.summary, a.name 
FROM book_author_tb ba 
INNER JOIN book_tb b ON b.id = ba.book_id
INNER JOIN author_tb a ON a.id = ba.author_id
WHERE (
  SELECT COUNT(*) 
  FROM book_author_tb
  WHERE book_id = b.id
) = 1
ORDER BY b.id

这个：

SELECT COUNT(*) FROM book_author_tb WHERE book_id = b.id

确保退回的书籍只有 1 个作者。
另一种方式（也许更有效）：

SELECT b.title, b.summary, a.name 
FROM (
  SELECT book_id, MAX(author_id) author_id 
  FROM book_author_tb
  GROUP BY book_id
  HAVING COUNT(*) = 1
) ba 
INNER JOIN book_tb b ON b.id = ba.book_id
INNER JOIN author_tb a ON a.id = ba.author_id
ORDER BY b.id