主页 / dba / 问题 / 283198
Accepted
PaxPrz
Asked: 2021-01-14 19:01:58 +0800 CST

Postgres 未在 array_agg 查询中返回数据，如下所示

  • 772

当特定图书馆中没有书籍数据时，就会出现问题。考虑以下工作场景。

桌子library

--------------------------------
| id |    name     |    owner  |
--------------------------------
|  1 |     ABC     |     A     |
|  2 |     DEF     |     D     |
|  3 |     GHI     |     G     |
--------------------------------

桌子books

--------------------------------
| id |    title    |  library  |
--------------------------------
|  a |     xxx     |     1     |
|  b |     yyy     |     1     |
|  c |     zzz     |     2     |
--------------------------------

现在，当我进行如下查询时：

SELECT library.name, array_agg(b.title) AS book_list FROM library, 
(SELECT title FROM books WHERE books.library = :library_no) as b 
WHERE library.id = :library_no GROUP BY library.id

该查询为库 1 和 2 生成输出，但不为库 3 生成输出。为什么以及如何解决此问题？（在没有图书馆书籍的情况下生成一个空列表）

所需输出：

----------------------
| name |    book_list |
----------------------
|  GHI |      {}      |   # or {null}
-----------------------

我什至试过coalesce如下：

SELECT library.name, coalesce(array_agg(b.title), ARRAY[]::VARCHAR[]) AS book_list FROM library, 
(SELECT title FROM books WHERE books.library = :library_no) as b 
WHERE library.id = :library_no GROUP BY library.id

Postgres 版本：12

postgresql join

2 个回答

  1. Best Answer

    ALEFT JOIN可以解决它，就像 Laurenz 提供的那样。

    LATERAL但我建议在子查询中使用 ARRAY 构造函数：

    SELECT l.name, b.book_list
FROM   library l
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT title
      FROM   books
      WHERE  library = l.id
      )
   ) b(book_list)
WHERE  l.id = :library_no;

    这样，您不需要在外部查询级别进行聚合，也不需要在GROUP BY那里。

    您也不需要COALESCE，因为空结果上的 ARRAY 构造函数已经生成了一个空数组 ( {})。

    并且对于一个小的选择应该更快library- 显然查询会获得单个给定库的结果。

    除此之外，您只需要将变量:library_no放在一个地方，就像演示的那样。

    关于LATERAL加盟：

    关于 ARRAY 构造函数：

    手册中关于连接表的基本知识。

    • 9

  2. 你需要一个左连接：

    ... FROM library
       LEFT JOIN books ON library.id = books.library

    然后你也会得到没有书的图书馆。

    • 5

相关问题