运行总和（）问题 [英] Running Total Sum() Problem

问题描述

我不知道为什么最后一条记录在余额栏中检索假值。

它应该是-200 ..

I don't know why the last record retrieving false value in the balance column.
It should be -200..

select 
    s.ID, s.date as Date,
    s.invno as Invoice_No,
    s.Debit as Db,s.Credit as Cr,
    (
        select sum(Debit)-sum(Credit) 
        from 
            GL_Table t 
        where 
            t.ID <= s.ID
    ) AS Balance
from 
    GL_Table s 
where 
    invno = 123456

ID         Date             Invoice_No   Debit   Credit    Balance
1	2014-03-16 00:00:00.000	123456	0	400	-400
2	2014-03-16 00:00:00.000	123456	100	0	-300
3	2014-03-16 00:00:00.000	123456	50	0	-250
4	2014-03-17 00:00:00.000	123456	20	0	-230
8	2014-03-18 00:00:00.000	123456	30	0	-250

请询问更多信息。如果需要的话。



提前致谢

please ask for more info. if needed.

Thanks in advance

推荐答案

我的假设是信用价值为50另一张ID值小于8且大于4的发票。



我添加 AND t.Invno = s.Invno 在子查询的 where 子句中，以确保子查询结果中只包含所选发票的行。



我添加按ID 订购，以确保正确订购结果集。

My assumption is that there is a credit value of 50 for another invoice that has an ID value less than 8 and greater than 4.

I added AND t.Invno = s.Invno in the subquery's where clause to ensure only rows for the selected invoice are included in the subquery result.

I added order by id to ensure proper ordering of the result set.

select 
    s.id,s.date as Date,
    s.invno as Invoice_No,
    s.Debit as Db,
    s.Credit as Cr,
    (
      select sum(Debit)-sum(Credit) from GL_Table t 
      where t.ID <= s.ID AND t.Invno = s.Invno
     ) AS Balance 
     from GL_Table s where invno = 123456 order by id

我的测试数据：

My test data:

CREATE TABLE [dbo].[GL_Table](
    [ID] [int] NOT NULL,
	[Date] [date] NOT NULL,
	[InvNo] [int] NOT NULL,
	[Debit] [int] NOT NULL,
	[Credit] [int] NOT NULL
) ON [PRIMARY]
Go
insert into gl_table values (1,'2014-03-06',123456,0,400);
insert into gl_table values (2,'2014-03-06',123456,100,0);
insert into gl_table values (3,'2014-03-06',123456,50,0);
insert into gl_table values (4,'2014-03-17',123456,20,0);
insert into gl_table values (8,'2014-03-18',123456,30,0);
insert into gl_table values (7,'2014-03-18',123457,0,50);

使用内部联接的替代解决方案：

Alternate Solution using Inner Join:

select 
    s.date as Date,
    s.invno as Invoice_No,
    s.Debit as Db,
    s.Credit as Cr,
   (sum(t.Debit)-sum(t.Credit)) as Balance  
From GL_Table s inner join GL_Table t on s.invno=t.invno and t.id<=s.id
Where s.invno = 123456 
Group by s.id,s.date,s.invno,s.debit,s.credit

这篇关于运行总和（）问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！