在sql server中减法的问题 [英] An issue with subtraction in sql server

问题描述

这是我的桌子结构

Here is My table structure

sno branch batch shed male female outstandingM outsatndingF
----------------------------------------------------------
1    15    7      12  100   120     100             120

2    15    7      12  40    60      40              60

3    15    7      12 100   180      100             180

我的问题是我必须减去未完成的值，即如果得到@ valueM = 220，@ valueF = 140。我已经减去了这样的出色的M，OutstandingF值

My problem is I have to subtract outstanding value i.e If got @valueM=220,@valueF=140 . I have subtract the outstandingM,OutstandingF value like this

sno branch batch shed male female outstandingM outsatndingF
-----------------------------------------------------------------
1    15    7      12  100   120     0            0

2    15    7      12  40    60     0             40

3    15    7      12 100   180     20            180

我不知道这可以和我分享他们的想法怎么做？

I have no idea about this can any one share their idea with me how to do this?

推荐答案

我假设你想要的是使用@valueM&将F变量称为可耗尽资产，并将outstandingM列计算为：

male - @valueM在之前的行中使用后剩余的内容。



直接的事情就是使用游标，但有几种方法可以使用或多或少的纯SQL，google搜索sql + cummulative会给你一些想法。



假设sno是主键，可以通过加入table2.PKID<来进行自联接以计算先前行的用尽量; table1.pkid，以便table2包含以前的所有行。

下面是计算@valueM&的剩余部分的示例。 F，可以从中得出优秀列。

I assume that what you want is to use the @valueM & F variables as "depletable assets" and calculate the outstandingM column as:
male - what remains of @valueM after it has been used in previous rows.

The straight forward thing would be to use a cursor, but the are several ways to do it using more or less plain SQL, googling on sql + cummulative will give you some ideas.

Assuming that sno is the primary key, one could do a self-join to calculate the used-up amount on previous rows by joining on table2.PKID<table1.pkid, so that table2 contains all previous rows.
Below is an example calculating the remains of @valueM & F, from which the "outstanding-columns" can be derived.

declare @valueM int
declare @valueF int
set @valueM=220
set @valueF=140

select t.sno,t.M,t.F,
case when @valueM-SUM(isnull(t2.M,0)) <0 then 0 else @valueM-SUM(isnull(t2.M,0))end as remainM,
case when @valueF-SUM(isnull(t2.F,0)) <0 then 0 else @valueF-SUM(isnull(t2.F,0)) end as remainF
from test_table t
left join test_table t2 on t2.sno < t.sno
group by t.sno,t.M,t.F
order by t.sno

这篇关于在sql server中减法的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！