我有一個表，其中包含兩列的值不是唯一的，這些值是自動生成的，我對此無能為力，無法編輯表、db 或自定義函式。
考慮到這一點，我已經在 sql server 中解決了這個問題，但它包含了一些 ms-access 中不存在的功能。
列是 Volume 和 ComponentID，這是我在 sql 中的代碼：
with rows as (
select row_number() over (order by volume) as rownum, volume
from test where componentid = 'S3')
select top 10
rowsMinusOne.volume, coalesce(rowsMinusOne.volume - rows.volume,0) as diff
from rows as rowsMinusOne
left outer join rows
on rows.rownum = rowsMinusOne.rownum - 1
樣本資料：
58.29168
70.57396
85.67902
97.04888
107.7026
108.2022
108.3975
108.5777
109
109.8944
預期成績：
| 體積 | 差異 |
|---|---|
| 58.29168 | 0 |
| 70.57396 | 12.28228 |
| 85.67902 | 15.10506 |
| 97.04888 | 11.36986 |
| 107.7026 | 10.65368 |
| 108.2022 | 0.4996719 |
| 108.3975 | 0.1952896 |
| 108.5777 | 0.1801834 |
| 109 | 0.4223404 |
| 109.8944 | 0.89431 |
I have solved the part of the coalesce by replacing it with NZ, I have tryed to use the DCOUNT to solve the row_number (How to show the record number in a MS Access report table?) but I reveive the error that it cannot find the function (I am reading the data by code, that is the only thing I can do).
I also tryed this but, as the answer says I need a column with a unique value which I do not have nor can create Microsoft Access query to duplicate ROW_NUMBER
uj5u.com熱心網友回復：
考慮：
SELECT TOP 10 Table1.ComponentID,
DCount("*","Table1","ComponentID = 'S3' AND Volume<" & [Volume]) 1 AS Seq, Table1.Volume,
Nz(Table1.Volume -
(SELECT Top 1 Dup.Volume FROM Table1 AS Dup
WHERE Dup.ComponentID = Table1.ComponentID AND Dup.Volume<Table1.Volume
ORDER BY Volume DESC),0) AS Diff
FROM Table1
WHERE (((Table1.ComponentID)="S3"))
ORDER BY Table1.Volume;
對于大型資料集，這可能會非常緩慢地執行。
替代解決方案：
構建計算差異的查詢，使用該查詢作為報告的來源，使用文本框 RunningSum 屬性計算序列號
VBA 回圈遍歷記錄集并將結果保存到“臨時”表
匯出到 Excel
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/314242.html