我让我的查询返回我想要的东西,但感觉它本可以用更好的方式来完成。有没有人可以教我一个更好的方法来回答这个问题,而且我知道“活动”栏是多余的,就这样吧。
sql fiddle:http://sqlfiddle.com/#!17/18152/53
books -suppose使用子查询,但我搞不懂--“列出所有图书馆的读者。如果他们借出了一本或多本书,就把这些书与读者对应起来。”
SELECT p.name,
(SELECT checked_in_date IS NULL AS active
FROM transactions t
WHERE p.id = t.patron_id AND b.isbn = t.isbn AND checked_in_date IS NULL),
b.title
FROM patrons p
LEFT OUTER JOIN transactions t
ON p.id = t.patron_id AND checked_in_date IS NULL
LEFT OUTER JOIN books b
ON b.isbn = t.isbn
所需的结果如下所示
name book_count(optional) title
------------------------------------------
Hermione Granger 0 (null)
Terry Boot 1 Advanced Potion-Making
Terry Boot 1 Fantastic Beasts and Where to Find Them
Padma Patil 0 (null)
Cho Chang 0 (null)
Cedric Diggory 0 (null)
发布于 2018-06-02 21:54:05
当我读到这个问题时,像这样的查询可以做你想要的事情:
SELECT p.name,
ARRAY_AGG(b.title)
FROM patrons p JOIN
transactions t
ON p.id = t.patron_id JOIN
books b
ON b.isbn = t.isbn
WHERE t.checked_in_date IS NULL
GROUP BY p.name
https://stackoverflow.com/questions/50657573
复制相似问题