sql - 使用 PostgreSQL 获取聚合的主键，按 SQL 查询分组

Question

我正在努力使用 PostgreSQL 创建涉及聚合的 SQL 查询。考虑下表:

CREATE TABLE thing (
  id INT NOT NULL PRIMARY KEY,
  price NUMERIC(10,2) NOT NULL,
  description VARCHAR(255) NOT NULL,
  url VARCHAR(255) NOT NULL,
  location_id INT NOT NULL REFERENCES location(id)
)

CREATE TABLE location (
  id INT NOT NULL PRIMARY KEY,
  type INT NOT NULL,
  name VARCHAR(255) NOT NULL
)

现在，我想获取每个 location.type = xxx 的地点的所有价格最低的事物记录。

类似于:

SELECT min(price) FROM thing
INNER JOIN location ON (thing.location_id = location.id)
WHERE type = xxx
GROUP BY location_id

这将列出每个类型为 xxx 的位置的最低价格，但我如何从表中获取这些列的行(或它们的主键)？

Answer 1

使用这个 PostgreSQL 扩展:

SELECT  DISTINCT ON (location.id) thing.*
FROM    location
JOIN    thing
ON      thing.location_id = location_id
WHERE   type = 1
ORDER BY
        location.id ASC, price ASC

这将只选择每个 location.id 的第一行。

由于您的行按 location.id 然后按 price 排序，因此这将是价格最低的行。

在新的 PostgreSQL 8.4 中，您还可以使用窗口函数:

SELECT  *
FROM    (
        SELECT  thing.*, ROW_NUMBER() OVER (PARTITION BY location_id ORDER BY price) AS rn
        FROM    location
        JOIN    thing
        ON      thing.location_id = location_id
        WHERE   type = 1
        ) q
WHERE   rn = 1