问题描述
亲爱的专家，
需要在差异中生成以下输出。
idno tran amount bal_difference
--------------------------------- ----------
001 1 200 0
001 2 500 300
001 3 1000 500
001 4 1700 700
计算如下
1.第一条记录的金额保持为零
2.如何使用sql语句生成预期的输出（即bal_difference）
谢谢
我的尝试：
这是一个特殊问题，检查是否有可能找不到。
Dear Expert,
Need to generate the following output in differences.
idno tran amount bal_difference
-------------------------------------------
001 1 200 0
001 2 500 300
001 3 1000 500
001 4 1700 700
The calculation is as follows
1. The amount of the first record stays as zero
2. How do I use sql statement to generate the expected output (i.e bal_difference)
Thanks
What I have tried:
This is a peculiar issue, checked for possibles not finding any.
推荐答案
DECLARE @MyTable TABLE(idno VARCHAR(3),[tran] INT, amount INT)
INSERT INTO @MyTable (idno, [tran], amount)
VALUES('001', 1, 200),
('001', 2, 500),
('001', 3, 1000),
('001', 4, 1700),
('002', 1, 100),
('002', 2, 300),
('002', 3, 1500),
('002', 4, 1750)
--1
SELECT a.idno, a.[tran], a.amount, ISNULL(a.amount-b.amount,0) AS bal_difference
FROM @MyTable a
LEFT JOIN @MyTable b ON b.[tran]=a.[tran]-1
--2
SELECT a.idno, a.[tran], a.amount, ISNULL(a.amount-b.amount,0) AS bal_difference
FROM @MyTable a
LEFT JOIN @MyTable b ON a.idno = b.idno AND b.[tran]=a.[tran]-1
结果＃1 - 错误的结果
Result #1 - WRONG result
idno tran amount bal_difference
001 1 200 0
001 2 500 300
001 2 500 400
001 3 1000 500
001 3 1000 700
001 4 1700 700
001 4 1700 200
002 1 100 0
002 2 300 100
002 2 300 200
002 3 1500 1000
002 3 1500 1200
002 4 1750 750
002 4 1750 250
结果＃2 - 正确结果
Result #2 - CORRECT result
idno tran amount bal_difference
001 1 200 0
001 2 500 300
001 3 1000 500
001 4 1700 700
002 1 100 0
002 2 300 200
002 3 1500 1200
002 4 1750 250
SELECT a.id, a.[no], a.tran_amount, ISNULL(a.tran_amount-b.tran_amount,0) AS bal_difference
FROM #MyTable a
LEFT JOIN #MyTable b ON b.no=a.no-1
完成测试查询
COMPLETE TESTED QUERY
CREATE TABLE #MyTable(id VARCHAR(3),[no] INT, tran_amount INT)
INSERT INTO #MyTable
SELECT '001', 1, 200
UNION
SELECT '001', 2, 500
UNION
SELECT '001', 3, 1000
UNION
SELECT '001', 4, 1700
SELECT a.id, a.[no], a.tran_amount, ISNULL(a.tran_amount-b.tran_amount,0) AS bal_difference
FROM #MyTable a
LEFT JOIN #MyTable b ON b.no=a.no-1
希望，它有帮助：）
Hope, it helps :)
这篇关于用于计算余额的Sql语句作为余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！