用于计算余额的Sql语句作为余额 [英] Sql statement to calculate differences in amount as balances

问题描述

亲爱的专家，



需要在差异中生成以下输出。





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.

推荐答案

另一种实现方法是使用： LEAD [ ^ ]和/或 LAG [ ^ ]功能。

详情请见： SQL SERVER - LEAD和LAG简介 - SQL Server 2012中引入的分析函数 - 使用Pinal访问SQL权限戴夫 [ ^ ]



注意：SQL Server 2012及更高版本是必需的！



正如我在 Suvendu Shekh ar Giri [ ^ ]，你必须在两个字段上自我连接表： idno 和 tran 以便能够保持这种关系在 idno 和 tran 之间。



检查以下示例：

Another way to achieve that is to use: LEAD[^] and/or LAG[^] function.
For further details, please see: SQL SERVER - Introduction to LEAD and LAG - Analytic Functions Introduced in SQL Server 2012 - Journey to SQL Authority with Pinal Dave[^]

Note: SQL server 2012 and higher is required!

As i mentioned in the comment to the solution #1 by Suvendu Shekhar Giri[^], you have to self join tables on both fields: idno and tran as to be able to keep the relationship between idno and tran.

Check on below example:

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

您可以使用自我加入这样做。

尝试以下内容 -

You can make use of SELF JOIN to do this.
Try something like following-

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语句作为余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！