PostgreSQL:选择列并从组中排除

Question

这个问题可能以不同的形式被问过，但我找不到答案。

我有表订单

date, quantity_ordered, unit_cost_cents, product_model_number, title

我愿意:

SELECT 
  model_number,
  title,
  SUM(unit_cost_cents  / 100.00 * quantity_ordered) as total
FROM orders
GROUP BY model_number
HAVING SUM(quantity_submitted) > 0
ORDER BY total DESC

但它也需要按 title 分组。

我的问题是我的 title 随着时间的推移而改变。我想保留标题并简单地显示/选择最新的 title 而不是按标题分组，这会使数字不同。

Answer 1

您可以使用子查询来获取最新的标题:

SELECT 
  model_number,
  (select max(title) from orders where date = (
    select max(date) from orders where model_number = o.model_number)
  ) title,
  SUM(unit_cost_cents  / 100.00 * quantity_ordered) as total
FROM orders o
GROUP BY model_number 
HAVING SUM(quantity_submitted) > 0
ORDER BY total DESC

我使用 select max(title) 而不是 select title 来确保子查询不会返回超过 1 行(以防万一)。