SQL計算金額問題

ms sql sql

CooperWu 2023-06-20 11:44:30 ‧ 2612 瀏覽

分享至

大家好，今天想詢問一個SQL的問題
每一個結帳單底下都有數量不等的專案，
我希望可以計算出每個專案的已收款、未收款的金額，
已收的總金額要先從序號1的開始計算，假如有剩下的錢再去計算序號2，以此類推

原始資料：

結果圖：

SQL：

SQL

再麻煩各位幫個忙，感謝

補充說明：

不好意思，我的問題可能沒有描述的很好，先以原始資料的結帳單A說明。
結帳單A收了50000塊。
專案一之1的金額為50000 - 30000塊，結果大於0代表錢有收完了，所以已收30000，未收0。
接下來專案一之2因為專案一之1已經收了3萬塊，所以這一筆的計算為：
(50000 - 30000) - 15000，結果也大於0，代表錢收完了所以已收15000 未收0。
專案一之3為 (50000 - 30000 - 15000) - 5000 已收5000，未收0
專案一之4為 (50000 - 30000 - 15000 - 5000)，因為這邊已經超過總金額了，所以已收0，未收3000。
大致上是這樣的流程

看更多先前的討論...收起先前的討論...

rogeryao iT邦超人 8 級 ‧ 2023-06-20 12:00:52 檢舉

請在貼一張你期望的示意圖

CooperWu iT邦新手 3 級 ‧ 2023-06-20 13:12:13 檢舉

有補上了

cliff_chen iT邦新手 4 級 ‧ 2023-06-20 14:16:08 檢舉

你這份原始資料沒有個專案的已收款欄位能拿來比較

廷 iT邦新手 4 級 ‧ 2023-06-20 14:20:03 檢舉

剛剛就在納悶，難不成已收款欄位的數值要我們自己生成嗎XD

lio88546 iT邦新手 5 級 ‧ 2023-06-25 07:57:05 檢舉

專案欄位，建議拆分為兩個欄位，會比較好做計算處理

一個專門存，專案A、專案B、專案C
一個存各專案的階段進度(1、2、3、4)

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

pickuse

iT邦新手 4 級 ‧ 2023-06-20 14:38:05

最佳解答

我覺得你太懶

CREATE TABLE [dbo].[TestTable](
	[結帳單] [nvarchar](255) NULL,
	[已收總金額] [float] NULL,
	[專案] [nvarchar](255) NULL,
	[專案金額] [float] NULL
) ON [PRIMARY]
INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單A', 50000, N'專案一之1', 30000)

INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單A', 50000, N'專案一之2', 15000)

INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單A', 50000, N'專案一之3', 5000)

INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單A', 50000, N'專案一之4', 3000)

INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單B', 30000, N'專案二之1', 15000)

INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單B', 30000, N'專案二之2', 9000)

INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單B', 30000, N'專案二之3', 7000)

INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單B', 30000, N'專案二之4', 3000)

INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單C', 1000, N'專案三之1', 3000)

INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單C', 1000, N'專案三之2', 2000)

INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單C', 1000, N'專案三之3', 5000)

INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單C', 1000, N'專案三之4', 4000)

Select * From TestTable;

WITH _CTE AS (
    SELECT 結帳單, 已收總金額, 專案, 專案金額,
           SUM(專案金額) OVER (PARTITION BY 結帳單 ORDER BY 專案 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 累積專案金額
    FROM TestTable
)

SELECT 結帳單, 已收總金額, 專案, 專案金額,
       CASE WHEN 累積專案金額 <= 已收總金額 THEN 專案金額
            WHEN 累積專案金額 - 專案金額 < 已收總金額 THEN 已收總金額 - (累積專案金額 - 專案金額)
            ELSE 0
       END AS 專案已收款,
       CASE WHEN 累積專案金額 <= 已收總金額 THEN 0
            WHEN 累積專案金額 - 專案金額 < 已收總金額 THEN 累積專案金額 - 已收總金額
            ELSE 專案金額
       END AS 未收款
FROM _CTE
ORDER BY 結帳單, 專案

回應
分享
檢舉

登入發表回應

PPTaiwan

iT邦好手 1 級 ‧ 2023-06-20 11:53:45

哇塞!! 現在人是怎樣，直接丟資料然後要別人幫你組出 TSQL ??

你是工程師還是 PM 啊!! 還不如不要做 IT 相關工作了。曾經有一位面試者相信他所說的，TSQL 什麼都會寫結果是連個 CRUD 都他媽的都不會，三個月後直接砍頭叫他滾..

回應 7
分享
檢舉

看更多先前的回應...收起先前的回應...

Felix iT邦研究生 2 級 ‧ 2023-06-20 12:22:31 檢舉

我們才不是 ChatGPT 呢！

CooperWu iT邦新手 3 級 ‧ 2023-06-20 14:02:30 檢舉

我不懂，這裡不是技術問答區嗎？
我描述問題，提供資料，就是希望可以獲得解答。
請問有哪一點你覺得不妥。還是你覺得這問題太簡單不准發問，請自行處理嗎？

PPTaiwan iT邦好手 1 級 ‧ 2023-06-20 14:19:00 檢舉

這裡是「技術問答」，而你的行為是代表你的工作就要別人代你去解決事情，你的行為就是你連 "TSQL技術研究都沒有做到" 就直接將想要的結果要去別人去幫你回應。那你自己去研究出什麼了，那你下達了什麼語法是你無法找出最後的結果??

而你最後的想要的就只是二個 TABLE JOIN 起來就這樣子就可以有解答，基本中基本你拿 SSMS 用 "在編輯器中設計查詢" 去 JOIN 也可以查出資料來，你連 SSMS 都不會用??

小處成就大事 iT邦研究生 3 級 ‧ 2023-06-20 14:39:00 檢舉

CooperWu
啊...我是認為你可以這樣問比較好
「我想要求得OOOO的結果，請問應該用哪個語法比較適合」
「我嘗試用XXX語法想要求的下圖結果，可是得到的結果都不正確，我可以怎麼修改我的語法」

CooperWu iT邦新手 3 級 ‧ 2023-06-20 14:51:25 檢舉

小處成就大事
 PPTaiwan
謝謝意見，我下次會改善問法

小處成就大事 iT邦研究生 3 級 ‧ 2023-06-20 14:58:22 檢舉

CooperWu
如果你有嘗試過，但沒有得到你想要的結果，我想大家都會很樂意幫你看程式碼。
可是如果你沒有在發問時描述出來，就會很容易被當成伸手牌。

我記得it幫曾經出現過幾次學校學生把他的功課直接丟上來問...
請問是要付講師鐘點費給回答的大大們嗎？

CooperWu iT邦新手 3 級 ‧ 2023-06-20 15:29:25 檢舉

小處成就大事
我了解了，謝謝你

登入發表回應

rogeryao

iT邦超人 8 級 ‧ 2023-06-20 13:14:58

【專案一之4】改成【專案一之14】驗證排序無誤

CREATE TABLE [dbo].[TestTable](
	[結帳單] [nvarchar](255) NULL,
	[已收總金額] [float] NULL,
	[專案] [nvarchar](255) NULL,
	[專案金額] [float] NULL
) ON [PRIMARY]

INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單A', 50000, N'專案一之1', 30000)
INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單A', 50000, N'專案一之2', 15000)
INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單A', 50000, N'專案一之3', 5000)
INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單A', 50000, N'專案一之14', 3000)
INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單B', 30000, N'專案二之1', 15000)
INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單B', 30000, N'專案二之2', 9000)
INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單B', 30000, N'專案二之3', 7000)
INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單B', 30000, N'專案二之4', 3000)
INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單C', 1000, N'專案三之1', 3000)
INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單C', 1000, N'專案三之2', 2000)
INSERT [dbo].[TestTable] ([結帳單], [已收總金額], [專案], [專案金額]) VALUES (N'結帳單C', 1000, N'專案三之3', 5000)

select [結帳單],[已收總金額],[專案],[專案金額],
case when pointA < 0 then [專案金額]
when pointA > [專案金額] then 0
else [專案金額] - pointA end '專案已收款',
[專案金額] - case when pointA < 0 then [專案金額]
when pointA > [專案金額] then 0
else [專案金額] - pointA end '專案未收款'
from (
select *,
Sum([專案金額]) Over (Partition By [結帳單],Left([專案],3) Order by [結帳單],Left([專案],3),
  Cast(SubString([專案],5,Len([專案]) - 4) As Int)
   rows between unbounded preceding and current row) - [已收總金額] as pointA
from TestTable) as mm

Demo
相同類型問題 :
參考資料 : 玩玩SQL~如何將欲扣的點數，逐筆計算需要使用的點數?