mysql - SQL 借记、贷记、余额

Question

嗨，我正在尝试找出应该使用什么 SQL 语句来生成此输出。我尽了最大努力，但我的查询不起作用。谢谢

STUDNO  | DEBIT  | CREDIT | BALANCE
1001    | 10000  |        | 10000
1001    |        |  5000  | 5000
1001    | 50     |        | 5050
1002    | 50     |        | 50
1003    | 0      | 0      |0 


these are the tables.

TBLSTUDENTS
ID   | NAME
1001 | A
1002 | B
1003 |



TBLPAYABLES
ID | Studno | Partic| Amount
1  | 1001   | TF    | 10,000
2  | 1001   | ID    | 50     
3  | 1002   | ID    |50

TBLPAYMENTS
ID | Studno  | Amount
1  | 1001    | 5000  

Answer 1

这样就可以了

select S.Studno, credit, Debit, row_number() Over (Partition by S.Studno, Order By ID as RN into #A
From
(
select S.Studno, null as credit, D.Amount as Debit
from TBLSTUDENTS S
left join  TBLPAYABLES D
union all
select S.Studno, C.Amount as credit, null as Debit, D.ID
left Join  TBLPAYMENTS C   
union all
select S.Studno, 0 as credit, 0 as Debit, 0
from TBLSTUDENTS S
where S.Studno not in (select Studno from TBLPAYABLES) or 
S.Studno not in (select Studno from TBLPAYMENTS)
) x

select S.Studno, Credit, Debit, C.ID        
( SELECT SUM(Coalesce(Debit,0) - Coalesce(Credit,0)) 
  FROM #A B
  WHERE A.Studno = B.Studno AND B.RN <= A.RN
) PrevSum
From #A A
order by S.Studno, RN