代码之家 › 专栏 › 技术社区 › Nick Fleetwood

如何对其他字段与其他表相等的字段求和

ms-access sql

Nick Fleetwood · 技术社区 · 6 年前

我有两张桌子, Assets 和 Main .我想创建一个查询,该查询将汇总 主要的 ,按中的每个帐户分组 资产 .但有一个陷阱:有时 amt 需要总结为积极的,有时是消极的。

在里面 资产 ,我有专栏 Account 和 Descript . 账户 将“1001”和其他内容作为文本保存; 描述 只是文字。

Account    Descript
--------------------------
1001       Cash
1101       Receivable

在里面 主要的 ,我有 Amt , Ac1 和 Ac2 .

金额 持有我们需要求和的金额
Ac1 和 Ac2 持有来自 资产 作为文本

在里面 主要的 ,当帐户标记为 Ac1 ,该交易对该账户是正面的。当账户被标记为 Ac2 ,交易金额为那是否定的。

就一项记录而言,主要是:

在“Amt”中有-1000.00,在“Ac1”中有“1001”,在“Ac2”中有“1101”。
在“Amt”中有2000.00,在“Ac1”中有“1001”,在“Ac2”中有“1101”。

数据:

Amt    Ac1    Ac2
-------------------
-1000  1001   1101
2000   1001   1101

因此,预期结果需要是:

Account    Descrip    TtlAmt
-------------------------------
1001       Cash        1000.00
1101       Receivable -1000.00

我有一些代码,但我不确定它是否有用。

SELECT 
    Asset.Account, Asset.Descrip AS Expr1, 
    SUM(Main.Amt) AS SumOfAMT, SUM(Main.Amt) AS Expr2
FROM 
    Asset 
LEFT JOIN 
    Main ON (Asset.ACCOUNT = Main.AC2) OR (Asset.ACCOUNT = Main.AC1)
GROUP BY 
    Asset.Account, Asset.Descrip;

非常清楚地说,我也有名为“负债”、“费用”等的表格。但我觉得我们可以只关注一个问题,因为其他问题应该在一些指导下落实到位。

我知道这与手头的问题无关,但在Excel中,我使用以下公式来实现这一点。

SUM(SUMIF([sum range], [criteria range], [criteria]), SUMIF([sum range], [criteria range], [criteria])*-1)

我想解释一下我的最终目标可能会有所帮助。

0 回复 | 直到 6 年前

June7 6 年前

假设Main中有一个唯一标识符字段。

考虑:

问题1

SELECT ID, "Ac1" AS Src, Ac1 AS Act, Amt FROM Main
UNION SELECT ID, "Ac2", Ac2, Amt*-1 FROM Main;

问题2

SELECT Query1.Act, Assets.Descrip, Sum(Query1.Amt) AS SumOfAmt
FROM Assets INNER JOIN Query1 ON Assets.Account = Query1.Act
GROUP BY Query1.Act, Assets.Descrip;

一应俱全

SELECT Query1.Act, Assets.Descrip, Sum(Query1.Amt) AS SumOfAmt
FROM Assets INNER JOIN
(SELECT Ac1 AS Act, Amt FROM Main
UNION SELECT Ac2, Amt*-1 FROM Main) AS Query1 
ON Assets.Account = Query1.Act
GROUP BY Query1.Act, Assets.Descrip;

Gordon Linoff 6 年前

从性能角度来看,相关子查询可能是更好的方法:

select a.*,
       ( (select nz(sum(m.amt), 0)
          from main as m
          where m.ac1 = a.account
         ) -
         (select nz(sum(m.amt), 0)
          from main as m
          where m.ac2 = a.account
         )
       ) as net_amount             
from assets as a;

特别是,这可以利用两个索引: main(ac1, amt) 和 main(ac2.amt) .

此外,它消除了整个结果集上的聚合。