我有一個商品系統的訪問資料庫。在這個資料庫中有兩個表：
餐桌采購
id article date amount price
1 341 2022-02-03 3 23
2 343 2022-02-04 5 18
3 343 2022-02-08 7 21
4 345 2022-02-17 3 12
5 348 2022-02-21 8 45
6 341 2022-03-02 5 27
餐桌銷售
id article date amount price
1 343 2022-02-23 3 28
2 341 2022-02-24 5 30
3 341 2022-03-03 2 35
4 345 2022-03-07 3 18
現在我想確定每篇文章產生了多少利潤。這意味著已售商品的金額*價格之和減去購買商品的金額*價格之和。困難的部分是，如果我只賣了 10 篇文章但購買了 15 篇文章，我需要購買 10 篇文章的總和，而不是 15 篇文章的總和。否則我的利潤將是負數。
我知道如何通過遍歷行來使用“常規”編程語言解決這個問題，但我想知道這是否可以通過單個 SQL 查詢（例如內部查詢或類似查詢）來完成。
任何幫助表示贊賞。
編輯：以下將是上述示例的所需結果：
id article amount bought sold profit
1 343 3 54 84 30
2 341 7 177 220 43
3 345 3 36 54 18
uj5u.com熱心網友回復：
我理解您的意思，我想編輯但被訊息“建議的編輯佇列已滿”阻止。
所以我想解釋一下你真正想要的是什么。
您希望購買的數量不超過出售的數量。（例如：第 341 條已售出 7 件和已購買件數 8，您希望將已購買和已售商品的數量限制為 7）
您想先出售先購買的物品 (FIFO)。（例如：第 341 條已購買了 2 次不同數量的商品（第一個 3 * 23，第二個 5 * 27）。您想按此順序賣出前 7 個數量：3 * 23 4 * 27，而不是此順序：5 * 27 2 * 23
select * into purchases
from (
select 1 id, 341 article, '2022-02-03' date, 3 amount, 23 price union all
select 2, 343, '2022-02-04', 5, 18 union all
select 3, 343, '2022-02-08', 7, 21 union all
select 4, 345, '2022-02-17', 3, 12 union all
select 5, 348, '2022-02-21', 8, 45 union all
select 6, 341, '2022-03-02', 5, 27
) t;
select * into sales
from (
select 1 id, 343 article, '2022-02-23' date, 3 amount, 28 price union all
select 2, 341, '2022-02-24', 5, 30 union all
select 3, 341, '2022-03-03', 2, 35 union all
select 4, 345, '2022-03-07', 3, 18
) t;
select * into range
from (
select 1 rnum union all
select 2 rnum union all
select 3 rnum union all
select 4 rnum union all
select 5 rnum union all
select 6 rnum union all
select 7 rnum union all
select 8 rnum union all
select 9 rnum union all
select 10 rnum
) t;
select min(s.id) id, s.article, count(s.article) amount,
sum(p.price) bought, sum(s.price) sold,
sum(s.price) - sum(p.price) profit
from
(
select row_number() over (partition by p.article order by p.id) seq,
p.article, p.price
from purchases p
inner join range r
on p.amount >= r.rnum
) p
inner join
(
select row_number() over (partition by s.article order by s.id) seq,
s.id, s.article, s.price
from sales s
inner join range r
on s.amount >= r.rnum
) s
on p.article = s.article and p.seq = s.seq
group by s.article
order by min(s.id);
結果：
id article amount bought sold profit
1 343 3 54 84 30
2 341 7 177 220 43
4 345 3 36 54 18
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=96decd2f92a8f77964a929013e825774
這是 SQL Server 解決方案。如您所知，MS Access 中沒有 row_number 函式。因此，您需要創建新表以生成具有 AutoNumber 型別的 seq 列，以在 MS Access 的情況下模擬 row_number。
如果這解決了您的問題并且您希望將其應用于 MS Access，請告訴我。
現在是 MS Access 的時候了。
我說你需要創建新表，但我錯了。你不需要。
我創建了RowNumberVBA 函式來模仿row_numberSQL 函式。
- Create Class Module and name it to
CRowNumberand paste following code.
Option Compare Database
Option Explicit
Private PartitionOld As String
Private RowNum As Integer
Private RowNums As New Dictionary
Public Table As String
Public Function RowNumber(Order As String, Partition As String) As Integer
' Order is used to prevent duplicated execution
If RowNums.Exists(Order) Then
' Already executed before, so use cached value
RowNum = RowNums(Order)
Else
' If Partition changed, reset to 1, otherwise increase 1
If (Partition <> PartitionOld) Then
RowNum = 1
Else
RowNum = RowNum 1
End If
'Debug.Print Table & "," & Order & ", " & Partition & "=" & PartitionOld & ":" & RowNum
' Cache for future use
RowNums.Add Order, RowNum
End If
PartitionOld = Partition
RowNumber = RowNum
End Function
- Create Module and paste following code.
Option Compare Database
Option Explicit
' To use Dictionary:
' [Tools] - [References] - Check [Microsoft Scripting Runtime]
' To clear cache:
' RowNumber "", "", "", True
Public Function RowNumber(Table As String, Order As String, Partition As String, Optional Reset As Boolean) As Integer
Dim rn As CRowNumber
Static RowNumbers As New Dictionary
If Reset Then
Set RowNumbers = New Dictionary
RowNumber = 0
Exit Function
End If
If RowNumbers.Exists(Table) Then
Set rn = RowNumbers.Item(Table)
Else
Set rn = New CRowNumber
rn.Table = Table
RowNumbers.Add Table, rn
End If
RowNumber = rn.RowNumber(Order, Partition)
End Function
- Create Query and paste following SQL.
select min(s.id) as id, s.article, count(s.article) as amount,
sum(p.price) as bought, sum(s.price) as sold,
sum(s.price) - sum(p.price) as profit
from
(
select RowNumber('p', p.article & '.' & p.id & '.' & r.rnum, p.article) as seq,
p.article, p.price
from purchases as p
inner join range as r
on p.amount >= r.rnum
group by p.article, p.id, r.rnum, p.price
) as p
inner join
(
select RowNumber('s', s.article & '.' & s.id & '.' & r.rnum, s.article) as seq,
s.id, s.article, s.price
from sales as s
inner join range as r
on s.amount >= r.rnum
group by s.article, s.id, r.rnum, s.price
) as s
on p.article = s.article and p.seq = s.seq
group by s.article
order by min(s.id);
- I used Dictionary in code to cache previous row number and table, to use Dictionary
- [Tools] - [References] - Check [Microsoft Scripting Runtime]
- Clear cache by calling RowNumber function in Immediate window before running query.
RowNumber "", "", "", True
- Run Query
You will see same result with SQL Server version.
You always need to clear cache before running query to use new cache for new query.
Difference between SQL Server and MS Access is
row_numberreplaced toRowNumberVBA functionSQL Server only need
id,partitionfor numbering, But VBA function need more:Table,article,id,rnumbecause VBA function cannot change order of rows.order by改為group by先獲取子查詢行，然后再加入，否則優化器將嘗試先加入，然后再獲取子查詢行。
很抱歉我沒有對代碼進行足夠的解釋，但是如果您對代碼有疑問，我會回答。
uj5u.com熱心網友回復：
嘗試這個：
SELECT
a.article, a.buy, b.sell, (a.sell-a.buy) profit
FROM (
SELECT
article, SUM(amount * price) buy
FROM purchases
GROUP BY article
) a
INNER JOIN (
SELECT
article, SUM(amount * price) sell
FROM sales
GROUP BY article
) b ON a.article = b.article
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/451415.html