如何在 SUM 函数中使用选择语句

Question

我有一张看起来像这样的表。

ID      |    Block     |    Flats  |    Ammount  |    Balance  |
1       |      1       |      GF-1 |    1000     |      500    |
2       |      1       |      GF-2 |    1000     |      500    |
3       |      2       |      GF-1 |    1000     |      500    |
4       |      2       |      GF-2 |    1000     |      1000   |
5       |      2       |      GF-2 |    1000     |      0      |

我想对此执行总和查询。我试过了

Select distinct A.Block,(Select Sum(Ammount) from t1 where block = A.block),(select Sum(Balance) from t1 where block = A.block) from t1 A

此查询工作正常，但其总和余额为 2500，但由于 ID 4 和 5 属于同一平面，因此我希望它对最新的余额求和，应该是 1500。我试图在 sum 函数中放置一个 select 语句，但这不起作用.那么我该如何才能做到这一点呢？

Answer 1

您可以

先为每个block/flats组合选择最新的id(使用 row_number() (，然后聚合：

Select t1.Block, sum(amount)
from (select t1.*,
             row_number() over (partition by block, flats order by id desc) as seqnum
      from t1
     ) t1
where seqnum = 1
group by t1.Block;

Answer 2

您可以使用以下查询

Select A.Block, Sum(A.Ammount), Sum(A.Balance)
from t1 A group by A.Flats order by A.ID;

排序依据子句用于相对于 ID 进行排序

Answer 3

我想你只是想SUM() group by block喜欢

select Block, sum(amount) totalamount,
sum(case when balance <> 0 then balance end) totalbalance
from t1
group by Block;

Answer 4

您可以使用

SUM(DISTINCT ...)

select Block, sum(DISTINCT amount) totalamount,
sum(DISTINCT case when balance <> 0 then balance end) totalbalance
from t1
group by Block;