需要SQL查询才能获得以下输出。 “付费金额”是指“付款金额”。 &安培; “收到的金额”列已存在于我的表中。想要产生“投资金额”使用SQL。 [英] Need SQL query to get following output. The "paid amount" & "received amount" columns are already present in my table. Want to generate "invested amount" using SQL.

问题描述

|Paid Amount | Received Amount | Invested Amount
| 10000      |    --           |  10000
|   --       |   8000          |   2000
|  5000      |    --           |   7000
|   --       |   4000          |   3000

我的尝试：



我可以在MS Office Excel中完成。但是想在我的C＃代码中执行此操作。

What I have tried:

I am able to do it in MS Office Excel. But want to do it in my C# code.

推荐答案

Select a.*, (Select SUM(Paid-Received) From #temp b where b.Code<=a.code) As Invested From #temp a Order By a.Code

你还没有表明表的结构但是我可以在Oracle中执行此操作（我不使用Sql Server）：

You have not indicated the structure of the table but I can do this in Oracle (I don't use Sql Server):

create table investments
(
 slno number primary key,
 type char(1),
 amount number 
)
;

我插入一些记录：

I insert some records:

insert into investments values (1, 'P', 10000);
insert into investments values (2, 'R', 8000);
insert into investments values (3, 'P', 5000);
insert into investments values (4, 'R', 4000);
insert into investments values (5, 'P', 2000);
insert into investments values (6, 'P', 6000);
insert into investments values (7, 'R', 3000);

选择查询是：

The select query is:

select slno, 
case type when 'P' then to_char(amount) else '---' end paid,
case type when 'R' then to_char(amount) else '---' end received,	
sum(amount * case type when 'P' then 1 else -1 end) over (order by slno) invested
from investments	
order by 1

您可以使用滞后函数获取前一行

You could use the lag function to get the previous row

SELECT value - lag(value) OVER (ORDER BY Id) FROM table

然后使用iif语句查看是否需要加/减

and then use the iif statement to see if you need to add or subtract

这篇关于需要SQL查询才能获得以下输出。 “付费金额”是指“付款金额”。 &安培; “收到的金额”列已存在于我的表中。想要产生“投资金额”使用SQL。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！