我有兩張桌子Issue和Received. 我想得到同一行問題的結果。
問題表：
| 不 | 編碼 | 件 |
|---|---|---|
| 1 | L0001 | 10 |
| 2 | L0002 | 25 |
| 3 | L0003 | 75 |
| 4 | L0004 | 12 |
| 5 | L0005 | 15 |
收到表：
| 不 | 編碼 | 件 |
|---|---|---|
| 1 | L0001 | 5 |
| 2 | L0001 | 5 |
| 3 | L0003 | 48 |
| 4 | L0003 | 12 |
| 5 | L0003 | 15 |
想要的結果：
| 不 | 編碼 | 國際空間站 | RECPCS | 巴爾 |
|---|---|---|---|---|
| 1 | L0001 | 10 | 5 | 5 |
| 2 | L0001 | 0 | 5 | 0 |
| 3 | L0003 | 75 | 48 | 27 |
| 4 | L0003 | 0 | 12 | 15 |
| 5 | L0003 | 0 | 15 | 0 |
| 6 | L0004 | 12 | 0 | 12 |
| 7 | L0005 | 15 | 0 | 15 |
SELECT ROW_NUMBER() OVER(PARTITION BY i.LCODE ORDER BY i.LCODE) as NO,
i.LCODE,i.PCS as ISSPCS,r.PCS as RECPCS,(i.PCS-r.PCS) as BAL
FROM Issue i
LEFT JOIN Received r ON i.LCODE = r.LCODE
找到獲得此結果的最佳方法謝謝。
uj5u.com熱心網友回復：
試試這個代碼：
SELECT ROW_NUMBER() OVER ( ORDER BY i.LCODE ASC, RunninngSum ASC) AS [NO]
, i.LCODE
, CASE WHEN RunninngSum > r.PCS THEN 0 ELSE i.PCS
END AS ISSPCS
, ISNULL( r.PCS, 0) AS RECPCS
, i.PCS - ISNULL( RunninngSum, 0) as BAL
FROM Issue i
LEFT JOIN Received r ON i.LCODE = r.LCODE
CROSS APPLY (
SELECT SUM( r1.PCS) AS RunninngSum
FROM Received r1
WHERE r1.LCODE = r.LCODE AND r1.[NO] <= r.[NO]
)x
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/422535.html
標籤：
上一篇：更改sql視圖的排序規則