Postgresql - 获取列中最大值的行

Question

我希望能够编写一个SQL查询，该查询可以返回某个分组中具有最大值的行的一些数据。以下是一个演示的示例：

有三个表：country（国家）、publisher（出版商）和book（书籍）。每个出版商属于一个国家，每个书籍都有一个出版商。定义如下：

Country(country_id, country_name)
Publisher(pub_id, pub_name, fk_pub_country)
Book(book_id, book_name, release_date, fk_book_publisher)

我想按国家选择 (country_id, book_name)，并按国家分组，以便每一行都包含该国最近发布的书籍名称。如果有多本书在同一天发布，则应获取id最高的那本。

如果仅使用 group by 和 max，无法包括书名。如果选择视图 (country_id, max_date) 并将其与 publisher 和 book 进行连接，可能会收到每个国家的多行数据。如何实现所需的结果？

- eko

---

作业？请添加示例表格数据和期望的结果，两者都要格式良好的文本。同时展示您当前的查询尝试，并描述出现了什么问题。 - jarlh

Answer 1

SELECT DISTINCT ON (country_id)
  country_id,
  book_name
FROM country
JOIN publisher ON fk_pub_country = country_id
JOIN book ON fk_book_publisher = pub_id
ORDER BY
  country_id,
  release_date DESC,
  book_id DESC

- Nick Barnes

---

谢谢！考虑到性能，这似乎也是一个不错的解决方案。 - eko

Answer 2

你说：

如果我只使用group by子句和max函数，我无法包含书名。

但是你不能让Postgres这样支配你。只需从结果数组中获取前n个排序记录中的第一个即可（未经测试）：

SELECT
  country_id,
  (array_agg(book_name))[1] as book_name
FROM country
JOIN publisher ON fk_pub_country = country_id
JOIN book ON fk_book_publisher = pub_id
GROUP BY country_id ORDER BY release_date DESC;

由于它们按照您的要求排序，(array_agg(...))[1]从列表中获取第一个项目，嘿，你有一锅炖菜了。

- Brandon

Answer 3

您可以使用子查询：

select c.country_id,
       (select b.book_name 
        from Book b 
             join Publisher p on p.pub_id = b.fk_book_publisher 
        where p.fk_pub_country = c.country_id 
        order by b.release_date desc, b.book_id desc limit 1) as book_name
from Country c

- Tobb