假設我有兩張桌子：
第一個表：帳戶表
| 帳戶名稱 | 帳戶代碼 |
|---|---|
| 客戶1 | 11 |
| 客戶2 | 111 |
| 客戶3 | 112 |
第二個表：賬戶明細
| 帳戶代碼 | 借方 | 信用 |
|---|---|---|
| 11 | 500 | 0 |
| 11 | 0 | 200 |
| 111 | 700 | 0 |
| 112 | 300 | 0 |
| 112 | 0 | 400 |
我需要進行查詢以生成下一個表：
| 帳戶名稱 | 總借記 | 總信用 | 累積余額 |
|---|---|---|---|
| 客戶1 | 500 | 200 | 300 |
| 客戶2 | 700 | 0 | 1000 |
| 客戶3 | 300 | 400 | 900 |
我試過這個，但它只顯示AccountCode：
select
sum(Debit) as TotalDebit,
sum(Credit) as TotalCredit,
Account_Code as AC
from AccountDetails as A
inner join Accounts as B ON A.AccountCode = B.AccountCode
where AccountCode like N'11%'
group by A.AccountCode
uj5u.com熱心網友回復：
select accountname
,sum(debit) as total_debit
,sum(credit) as total_credit
,sum(coalesce(sum(debit),0)-coalesce(sum(credit),0)) over(order by accountname) as cumulative_balance
from accounts a join account_details ad on ad.accountcode = a.accountcode
group by accountname
| 帳戶名稱 | total_debit | 總信用 | 累積余額 |
|---|---|---|---|
| 客戶1 | 500 | 200 | 300 |
| 客戶2 | 700 | 無效的 | 1000 |
| 客戶3 | 300 | 400 | 900 |
小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/517913.html
標籤：sqlsql服务器加入