标题: 火燒眉毛!累加查詢 [打印本页] 作者: Susie 时间: 2002-9-4 21:54 标题: 火燒眉毛!累加查詢 在一個查詢中, 我需要 qty 按降序排序, CumPct 中的29.81%是 code: c 所占product: A 的百分率, 而53.90%則是 code: d 所占 product: A的百分率與 code: c 的和. 76.76% 是 code: b 所占 product: A的百分率與code c, d 的百分率之和.依此類推, 直至product: A的百分率為100%.
product code qty CumPct
A c 1511 29.81%
A d 1221 53.90%
A b 1159 76.76%
A e 589 100.00%
A a 589 100.00%
B e 974 40.94%
B c 541 63.68%
B d 360 93.95%
B a 360 93.95%
B b 144 100.00%
SELECT sort.code, sort.product, sort.qty, DSum("[sumqty]","sort","[rmy]>=" & [rmy] & " and [product]='" & [product] & "'")/DSum("[rmy]","sort","[product]='" & [product] & "'") AS CumPct
FROM sort
ORDER BY sort.product, sort.sumqty DESC;