基于prevoius行数据自动计算sql表中的列 [英] Auto calculating columns in a sql table based on prevoius row data

问题描述

我使用 sql server 作为后端，用于在 asp.net c# 中开发我的应用程序.现在我需要创建一个带有自动计算列(期末余额)的表，如下所示:

I am using sql server as back-end for developing my application in asp.net c#. Now i need to create a table with an auto calculating column(closing balance) as shown below:

Date    in stock    out stock   closing balance
2/3/2013    0             0          0
3/3/2013    10            5          5
5/3/2013    10            52        -37
6/3/2013    52            7          8
7/3/2013    6             50        -36
8/3/2013    10            12        -38

这里每天的期末余额取决于前一天的期末余额

Here the closing balance for each day depends on the closing balance of the previous day

例如今天的期末余额=(前一天期末余额+今天的库存)-(今天的库存)当我在表格中添加日期为 4/3/2013 的新条目时，表格必须如下所示

For Example, today's closing balance=(previous day closing balance+today's instock)-(today's outstock) When i add a new entry dated 4/3/2013 to the table,the table must be shown as below

Date    in stock    out stock   closing balance
2/3/2013    0            0              0
3/3/2013    10           5              5
4/3/2013    20          15             10
5/3/2013    10          52            -32
6/3/2013    52           7             13
7/3/2013    6           50            -31
8/3/2013    10          12            -33

我使用 microsoft excel(使用公式)制作了这个，但我需要使用 sql server 表为我的应用程序实现它.任何帮助将不胜感激.

i made this using microsoft excel(using formulas),but i need to achieve it using sql server table for my application. Any help will be appreciated.

推荐答案

假设

1.你的表结构就像

Date | In Stock | Out Stock

2.在计算余额之前，您将插入一个新列.

2.You will insert a New Columns before calculating the balance.

3.Date 是一个 Primary Column (唯一 + 非空)

3.Date is an Primary Column (Unique + Not NULL)

采用上述假设:

如果您想在 C#

1.创建一个临时表并使用Rank()分配Row Number

1.Create a temp table and assigned Row Number using Rank()

select 
rank() OVER (ORDER BY [Date]) as [Rank],
t1.[Date],
t1.[in stock],
t1.[out stock]
--,t1.[in stock]-t1.[out stock] balance
into #temp1
from (your table name)
;

2.现在您将使用上面的临时表来获得余额

2.Now you will be using the above temp table to get the Balance

WITH x AS
(
    SELECT 
        [Rank],
        [Date],
        [in stock],
        [out stock],
        bal=([in stock]-[out stock])
    FROM #temp1
    WHERE [Rank] = 1
    UNION ALL
    SELECT 
        y.[Rank],
        y.[Date],
        y.[in stock],
        y.[out stock],
        x.bal+(y.[in stock]-y.[out stock])
    FROM x INNER JOIN #temp1 AS y
    ON y.[Rank] = x.[Rank] + 1
)
SELECT 
    [Date],
    [in stock],
    [out stock],
    Balance = bal
FROM x
ORDER BY Date
OPTION (MAXRECURSION 10000);

这是SQL Fiddle，您可以在这里可以验证.

Here is the SQL Fiddle where you can verify.

这篇关于基于prevoius行数据自动计算sql表中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！