在Postgres中使用带有汇总和groupby的子查询进行更新 [英] Update using a subquery with aggregates and groupby in Postgres

问题描述

我正尝试用该列的最大值按另一列分组来更新表中的列。

I'm trying to update a column in a table with the max value of that column grouped by another column.

例如，假设我们有一个表具有两列的命名交易：数量和商品名称。出于任何原因，我们希望将数量设置为等于每个 item_name找到的最大数量 。

So for example, say we have a table named transactions with two columns: quantity and item_name. And for whatever reason we want to set quantity equal to the maximum quantity found for each item_name.

我很沮丧，在SQL中做这样的事情很不好，但是到目前为止，我的意思是：

I'm pretty stumped and bad at doing things like this in SQL, but here's what I have so far:

UPDATE transactions 
SET
quantity = subquery.quantity
FROM (select max(quantity), item_name
      from transaction group by item_name) AS subquery
WHERE  and item_name = subquery.item_name;

推荐答案

除了@Gordon已经指出的语法错误出来，排除空更新通常是个好主意：

In addition to your syntax errors that @Gordon already pointed out, it is regularly a good idea to exclude empty updates:

UPDATE transaction t
SET    quantity = sub.max_quantity
FROM  (
   SELECT item_name, max(quantity) AS max_quantity
   FROM   transaction
   GROUP  BY 1
   ) sub
WHERE t.item_name = sub.item_name
AND  t.quantity IS DISTINCT FROM sub.max_quantity;

无需更改任何内容就无需编写新的行版本（几乎花费全部费用）。 （除非您要触发一个触发器。）

No need to write new row versions (at almost full cost) without changing anything. (Except if you want to fire a trigger.)

这篇关于在Postgres中使用带有汇总和groupby的子查询进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！