ID debit credit sum_debit
---------------------------------
1 150 0 150
2 100 0 250
3 0 50 200
4 0 100 100
5 50 0 150我有这个表,我的问题是如何获得sum_debit列,它是前一行借方减贷方(sum_debit = sum_debit + debit - credit)的sum_debit的总和。每一个新的行,我输入借项,但信贷数据为零,或通过输入贷方和借方的值为零。如何获取sum_debit
发布于 2013-06-12 02:21:29
假设"have“是您的数据表,这应该是一个ANSI SQL解决方案:
select h.*, sum(i.debit) as debsum, sum(i.credit) as credsum, sum(i.debit) - sum(i.credit) as rolling_sum
from have h inner join have i
on h.id >= i.id
group by h.id, h.debit, h.credit
order by h.id通常,解决方案是将该行连接到该行之前的所有行,并提取这些行的总和,然后按所有内容分组,以返回到您期望的每一行。比如this question。
发布于 2013-06-12 02:21:36
在SQL-Server2012中,您可以使用新添加的 or 子句:
SELECT
ID, debit, credit,
sum_debit =
SUM(debit - credit)
OVER (ORDER BY ID
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
)
FROM
CreditData
ORDER BY
ID ;在中测试
我们可以只在那里使用OVER(ORDER BY ID),结果将是相同的。但随后将使用缺省值,即RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,并且存在效率差异(运行总计时应首选ROWS)。
@Aaron Bertrand有一篇很棒的文章,它对各种计算运行总数的方法进行了彻底的测试:
对于以前版本的SQL-Server,您必须使用一些其他方法,如自联接、递归CTE或游标。这是一个游标解决方案,盲目地复制自Aaron的博客,并根据您的问题调整表和列:
DECLARE @cd TABLE
( [ID] int PRIMARY KEY,
[debit] int,
[credit] int,
[sum_debit] int
);
DECLARE
@ID INT,
@debit INT,
@credit INT,
@RunningTotal INT = 0 ;
DECLARE c CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT ID, debit, credit
FROM CreditData
ORDER BY ID ;
OPEN c ;
FETCH NEXT FROM c INTO @ID, @debit, @credit ;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RunningTotal = @RunningTotal + (@debit - @credit) ;
INSERT @cd (ID, debit, credit, sum_debit )
SELECT @ID, @debit, @credit, @RunningTotal ;
FETCH NEXT FROM c INTO @ID, @debit, @credit ;
END
CLOSE c;
DEALLOCATE c;
SELECT ID, debit, credit, sum_debit
FROM @cd
ORDER BY ID ;在中测试
https://stackoverflow.com/questions/17050660
复制相似问题