时间戳

首页 > 解决方案 > MsSQL 贷记、借记和余额

sql - MsSQL 贷记、借记和余额

问题描述

我有一张如下表。

Name|   Date        |   Debit   |   Credit
-------------------------------------------
A   |   02.02.2020  |   10,00   |   0,00
A   |   03.02.2020  |   20,00   |   0,00
A   |   04.02.2020  |   0,00    |   30,00
A   |   05.02.2020  |   20,00   |   0,00
A   |   06.02.2020  |   40,00   |   0,00
A   |   07.02.2020  |   50,00   |   0,00
A   |   08.02.2020  |   15,00   |   0,00
A   |   09.02.2020  |   20,00   |   0,00
A   |   10.02.2020  |   10,00   |   0,00
A   |   11.02.2020  |   20,00   |   0,00
A   |   12.02.2020  |   0,00    |   50,00
A   |   13.02.2020  |   0,00    |   60,00
A   |   14.02.2020  |   20,00   |   15,00

B   |   20.01.2020  |   10,00   |   0,00
B   |   21.01.2020  |   20,00   |   0,00
B   |   22.01.2020  |   40,00   |   0,00
B   |   23.01.2020  |   0,00    |   100,00
B   |   24.01.2020  |   20,00   |   0,00
B   |   25.01.2020  |   10,00   |   0,00
B   |   26.01.2020  |   0,00    |   10,00
B   |   27.01.2020  |   10,00   |   0,00

我想用这个表创建一个“视图”。首先，所有“学分”将根据“名称”相互添加。然后，从总“贷方”中减去“借方”。此处获得的值将在第一个日期写入“余额”列。然后从这里，“借方”列上的值将按顺序减去并写入今天日期的“余额”列。

我想要获得的“视图”如下图所示。

Name|   Date        |   Debit   |   Credit  |   Balance |   Credit Total
-------------------------------------------------------------------------
A   |   02.02.2020  |   10,00   |   0,00    |   145,00  |   155,00
A   |   03.02.2020  |   20,00   |   0,00    |   125,00  |
A   |   04.02.2020  |   0,00    |   30,00   |   125,00  |
A   |   05.02.2020  |   20,00   |   0,00    |   105,00  |
A   |   06.02.2020  |   40,00   |   0,00    |   65,00   |
A   |   07.02.2020  |   50,00   |   0,00    |   15,00   |
A   |   08.02.2020  |   15,00   |   0,00    |   0,00    |
A   |   09.02.2020  |   20,00   |   0,00    |   -20,00  |
A   |   10.02.2020  |   10,00   |   0,00    |   -30,00  |
A   |   11.02.2020  |   20,00   |   0,00    |   -50,00  |
A   |   12.02.2020  |   0,00    |   50,00   |   -50,00  |
A   |   13.02.2020  |   0,00    |   60,00   |   -50,00  |
A   |   14.02.2020  |   20,00   |   15,00   |   -70,00  |

B   |   20.01.2020  |   10,00   |   0,00    |   100,00  |   110,00
B   |   21.01.2020  |   20,00   |   0,00    |   80,00   |
B   |   22.01.2020  |   40,00   |   0,00    |   40,00   |
B   |   23.01.2020  |   0,00    |   100,00  |   40,00   |
B   |   24.01.2020  |   20,00   |   0,00    |   20,00   |
B   |   25.01.2020  |   10,00   |   0,00    |   10,00   |
B   |   26.01.2020  |   0,00    |   10,00   |   10,00   |
B   |   27.01.2020  |   10,00   |   0,00    |   0,00    |

我可以在“mvc”中执行此操作，如下所示：

var creditTotal = Model.Sum(x => x.Credit);

foreach (var item in Model.OrderBy(x => x.Date))
{
    <tr>
        <td>@item.Name</td>
        <td>@item.Date</td>
        <td>@item.Debit</td>
        <td>@item.Credit</td>
        <td>@creditTotal - item.Debit</td>
    </tr>
    creditTotal = creditTotal - item.Debit;
}

但如果我能在 SQL View 中做到这一点会好得多。有没有办法在sql视图中做到这一点？

标签： sqlsql-server

解决方案

您可以使用窗口函数：

select t.*,
       (sum(credit) over (partition by name) -
        sum(debit) over (partition by name order by date)
       ) as balance,
       (case when row_number() over (partition by name order by date) = 1
             then sum(credit) over (partition by name)
        end) as credit_total
from t

推荐阅读