sql - 在已删除行的查询中使用 SUM 函数

Question

我在 PostgreSQL 中有下表 A:

idMain  idSub    quantity
49;     83604;   3000.0000
49;     84361;   16000.0000
49;     84268;   30
47;     84268;   10.0000

我有一个选择 idMain 并显示有关它的数据的查询。我需要为此查询添加另一列，以便它将所有 idMain 的 SUM quantity (不包括我自己的 quantity) 其中 idSub 相同。

所需的数据是:

idMain  idSub    quantity      newcolumn
49;     83604;   3000.0000         0                //no other 83604 so 0
49;     84361;   16000.0000        0                //no other 84361 so 0
49;     84268;   30                10.0000          //other 84268 is 10.000 (and don't count my own 30)

首先我做了:

select idMain,IdSub,coalesce( SUM(quantity) OVER (PARTITION BY idSub) - quantity ,0)
from A
where idMain=49

注意 WHERE 上的查询删除了 idMain=47 所以我所做的不起作用，因为它没有对 idMain=47 求和

然后我尝试了:

select idMain,IdSub,(SELECT coalesce( SUM(quantity) OVER (PARTITION BY a2.idSub) - a2.quantity,0) from A a2 where a2.idSub=a.idSub) 
from A
where idMain=49

但这也行不通。

有人知道怎么弄吗？看起来我很亲近。

Answer 1

Gordon 的回答返回了正确的结果，但这将首先计算所有行的组总和，然后再对 idMain 应用过滤器。

您的第二个查询也几乎是正确的(并且应该首先应用过滤器)，您只需要删除 OVER 并改用简单的标量子查询:

select idMain,IdSub,quantity,
  coalesce((SELECT SUM(quantity) 
            from A a2 
            where a2.idSub=a.idSub) - quantity,0) as new column
from A
where idMain=49;