sql - Access DB 在知道总数之前对列的总数进行行项目计算

Question

[编辑]

作为记录，这里是查询的问题部分，现在正在运行:

SELECT 
 m.groupNum, 
 t.ea,
 ( t.ea - ( t.ea * m.margin )) AS estCost,
 (( t.ea - ( t.ea * m.margin )) * t.quantity ) AS salesSub,
 ((( t.ea - ( t.ea * m.margin )) * t.quantity ) / 
   (
    SELECT SUM(( t2.ea - ( t2.ea * m.margin )) * t2.quantity )
    FROM temp as t2
    INNER JOIN masters as m2 
    ON t2.mod = m2.mod
    WHERE m2.groupNum = m.groupNum
    )
  ) AS salesPercent
etc...

[结束编辑]

我想我需要一个查询，它可以在给定 (groupNum) 范围内的所有其余记录上插入值后，根据列的总值递归更新自身。

我已经有了 estCost 和 salesSub 字段。现在我需要对 salesPercent 字段进行计算，这涉及了解给定集合 (groupNum) 中所有 salesSub 记录的总数。

salesPercent = 
  ( salesSub / [the sum total of all salesSub amounts for each groupNum] )

(剪断)

SELECT 
 m.id, 
 t.priceEach,
 ( t.priceEach - ( t.priceEach * m.margin )) AS estCost,
 (( t.priceEach - ( t.priceEach * m.margin )) * t.quantity ) AS salesSub
 -- is it possible to perform calculation on salesPercent here?
INTO output

FROM financeMasters AS m
INNER JOIN temp AS t .......

(结束片段)

我有这个...

------
output
---------------------------------------------------------------
id | groupNum | priceEach | estCost | salesSub | salesPercent |
---------------------------------------------------------------
1  | apple    | 150.00    |  90.00  | 90.00    |   
2  | apple    | 100.00    |  60.00  | 60.00    |   
3  | apple    |  50.00    |  30.00  | 30.00    |   

但在知道总数之前，如何计算 salesSub 总数(在本例中为 180.00)的 salesPercent？

Answer 1

您可能必须使用子选择对每一行进行数学计算，没有办法“返回”并更改之前的行。

像这样:

SELECT 
 m.id, 
 t.priceEach,
 ( t.priceEach - ( t.priceEach * m.margin )) AS estCost,
 (( t.priceEach - ( t.priceEach * m.margin )) * t.quantity ) AS salesSub,
 ((( t.priceEach - ( t.priceEach * m.margin )) * t.quantity )
     / (SELECT SUM(( t2.priceEach - ( t2.priceEach * m.margin )) * t2.quantity )
        FROM financeMasters AS m2
            INNER JOIN temp AS t2 .....
        WHERE m2.groupNum = m.groupNum))
     AS salesPercent
INTO output

FROM financeMasters AS m
INNER JOIN temp AS t .......

是的，它非常丑陋。由于不知道你是如何进行连接的，我不得不省略一些细节，而且我不确定 groupNum 来自哪个表，你没有在任何地方显示。