本文介绍了在SQL Server中，第一行最后一列是如何添加第二行列？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
你好先生，
DOMAIN：SQL SERVER
表名：pindex
Hello Sir,
DOMAIN: SQL SERVER
Table Name: pindex
ICODE ITEM MONTHS YEARS GRN MRS MRN STOCK
33 CLUTCH 4 2010 1.000 0 1 2.000
155 HOSE 4 2010 2.000 0 1 3.000
179 BRAKE CHAMBER 4 2010 2.000 0 1 3.000
179 BRAKE CHAMBER 4 2010 20.000 0 1 21.000
179 BRAKE CHAMBER 4 2010 32.000 0 1 33.000
409 ENGINE OIL 4 2010 210.000 0 11 221.000
419 FUEL 4 2010 200.000 0 0 200.000
419 FUEL 4 2010 210.000 0 0 210.000
551 DRIVER SEAT 4 2010 10.000 0 19 29.000
602 WIRE 4 2010 272.000 0 14 286.000
771 WASTE 4 2010 500.000 0 0 500.000
771 WASTE 4 2010 500.000 0 4 504.000
771 WASTE 4 2010 1500.00 0 0 1500.000
771 WASTE 4 2010 1500.00 0 4 1504.000
公式：股票= GRN-MRS + MRN
这里，我做了一个 STOCK = GRN-MRS + MRN 的计算。现在我想要如何将第一行的最后一列（STOCK）添加到某些列的第二行（GRN）中，然后执行相同的计算（STOCK = STOCK + GRN-MRS + MRN）并获得股票新价值。这个过程将一直持续到ICODE相同。
例如表格：
Formula: STOCK=GRN-MRS+MRN
Here, i did one calculation that is STOCK=GRN-MRS+MRN. Now I want "How to add last column(STOCK) of first row into second row of some columns(GRN) and then do same calculation(STOCK=STOCK+GRN-MRS+MRN) and getting stock new value.This process will continue till ICODE is same. "
for example table:
icode Months Years grn mrs mrn stock
1 4 2010 400 200 50 250
1 4 2010 60+(250)=310 300 0 oldvalue(60) newvalue(10)
1 4 2010 40+(10)=50 0 0 oldvalue(40) newvalue(50)
请先生...查找此问题的结果查询..
Pls sir ...Find result query for this Problem..
推荐答案
DECLARE @pindex TABLE (ID INT IDENTITY(1,1),ICODE INT, ITEM VARCHAR(100), MONTHS SMALLINT,YEARS SMALLINT,GRN NUMERIC(10,3),MRS NUMERIC(10,3),MRN NUMERIC(10,3), STOCK NUMERIC(10,3))
INSERT INTO @Pindex (ICODE, ITEM, MONTHS, YEARS, GRN, MRS, MRN, STOCK)
SELECT 33,'CLUTCH',4,2010,1,NULL,1,2 UNION ALL
SELECT 155 ,'HOSE',4,2010,2,NULL,1,3 UNION ALL
SELECT 179,'BRAKE CHAMBER',4,2010,2,NULL,1,3 UNION ALL
SELECT 179,'BRAKE CHAMBER',4,2010,20,NULL,1,21 UNION ALL
SELECT 179,'BRAKE CHAMBER',4,2010,32,NULL,1,33 UNION ALL
SELECT 409,'ENGINE OIL',4,2010,210,NULL,11,221 UNION ALL
SELECT 419,'FUEL',4,2010,200,NULL,0,200 UNION ALL
SELECT 419,'FUEL',4,2010,210,NULL,0,210 UNION ALL
SELECT 551,'DRIVER SEAT',4,2010,10,NULL,19,29 UNION ALL
SELECT 602,'WIRE',4,2010,272,NULL,14,286 UNION ALL
SELECT 771,'WASTE',4,2010,500,NULL,0,500 UNION ALL
SELECT 771,'WASTE',4,2010,500,NULL,4,504 UNION ALL
SELECT 771,'WASTE',4,2010,1500,NULL,0,1500 UNION ALL
SELECT 771,'WASTE',4,2010,1500,NULL,4,1504 UNION ALL
SELECT 997,'SILENCER',4,2010,5,NULL,0,5 UNION ALL
SELECT 1362,'REFLECTOR',4,2010,250,NULL,50,300 UNION ALL
SELECT 1363,'REFLECTOR',4,2010,200,NULL,50,250 UNION ALL
SELECT 409,'ENGINE OIL',5,2010,210,NULL,11,221 UNION ALL
SELECT 419,'FUEL',5,2010,210,NULL,0,210 UNION ALL
SELECT 1616,'COVER',5,2010,10,NULL,1,11 UNION ALL
SELECT 1921,'U-BOLT',5,2010,1,NULL,4,5 UNION ALL
SELECT 1921,'U-BOLT',5,2010,12,NULL,4,16 UNION ALL
SELECT 1921,'U-BOLT',5,2010,20,NULL,4,24 UNION ALL
SELECT 419,'FUEL',6,2010,20,NULL,0,20 UNION ALL
SELECT 419,'FUEL',6,2010,143,NULL,0,143 UNION ALL
SELECT 771,'WASTE',6,2010,500,NULL,0,500 UNION ALL
SELECT 771,'WASTE',6,2010,500,NULL,4,504 UNION ALL
SELECT 1362,'REFLECTOR',6,2010,750,NULL,50,800 UNION ALL
SELECT 1363,'REFLECTOR',6,2010,750,NULL,50,800 UNION ALL
SELECT 1393,'PLATE',6,2010,78.5,NULL,80,158.5 UNION ALL
SELECT 1921,'U-BOLT',6,2010,10,NULL,4,14 UNION ALL
SELECT 155,'HOSE',7,2010,20,NULL,1,21 UNION ALL
SELECT 155,'HOSE',7,2010,30,NULL,1,31 UNION ALL
SELECT 179,'BRAKE CHAMBER',7,2010,4,NULL,1,5 UNION ALL
SELECT 179,'BRAKE CHAMBER',7,2010,6,NULL,1,7 UNION ALL
SELECT 179,'BRAKE CHAMBER',7,2010,8,NULL,1,9 UNION ALL
SELECT 179,'BRAKE CHAMBER',7,2010,10,NULL,1,11 UNION ALL
SELECT 409,'ENGINE OIL',7,2010,420,NULL,11,431 UNION ALL
SELECT 419,'FUEL',7,2010,180,NULL,0,180 UNION ALL
SELECT 419,'FUEL',7,2010,200,NULL,0,200 UNION ALL
SELECT 419,'FUEL',7,2010,210,NULL,0,210 UNION ALL
SELECT 419,'FUEL',7,2010,270,NULL,0,270 UNION ALL
SELECT 551,'DRIVER SEAT',7,2010,19,NULL,19,38 UNION ALL
SELECT 771,'WASTE',7,2010,500,NULL,0,500 UNION ALL
SELECT 771,'WASTE',7,2010,500,NULL,4,504 UNION ALL
SELECT 771,'WASTE',7,2010,750,NULL,0,750 UNION ALL
SELECT 771,'WASTE',7,2010,750,NULL,4,754 UNION ALL
SELECT 771,'WASTE',7,2010,1000,NULL,0,1000 UNION ALL
SELECT 771,'WASTE',7,2010,1000,NULL,4,1004 UNION ALL
SELECT 997,'SILENCER',7,2010,10,NULL,0,10 UNION ALL
SELECT 1362,'REFLECTOR',7,2010,500,NULL,50,550 UNION ALL
SELECT 1921,'U-BOLT',7,2010,20,NULL,4,24 UNION ALL
SELECT 2980,'TIGHTER',7,2010,150,NULL,3,153 UNION ALL
SELECT 3786,'D-SHAKLE',7,2010,400,NULL,6,406 UNION ALL
SELECT 64,'BELT',8,2010,10,NULL,0,10 UNION ALL
SELECT 66,'ENGINE BED',8,2010,10,NULL,0,10 UNION ALL
SELECT 179,'BRAKE CHAMBER',8,2010,12,NULL,1,13 UNION ALL
SELECT 409,'ENGINE OIL',8,2010,36,NULL,11,47 UNION ALL
SELECT 419,'FUEL',8,2010,30,NULL,0,30 UNION ALL
SELECT 419,'FUEL',8,2010,210,NULL,0,210 UNION ALL
SELECT 551,'DRIVER SEAT',8,2010,10,NULL,19,29 UNION ALL
SELECT 771,'WASTE',8,2010,50,NULL,0,50 UNION ALL
SELECT 771,'WASTE',8,2010,50,NULL,4,54 UNION ALL
SELECT 1362,'REFLECTOR',8,2010,50,NULL,50,100 UNION ALL
SELECT 1362,'REFLECTOR',8,2010,100,NULL,50,150 UNION ALL
SELECT 1363,'REFLECTOR',8,2010,50,NULL,50,100 UNION ALL
SELECT 2980,'TIGHTER',8,2010,16,NULL,3,19 UNION ALL
SELECT 4078,'SEALING RING',8,2010,5,NULL,1,6
SELECT M.ID,(ROW_NUMBER() OVER(PARTITION BY M.ICODE ORDER BY M.ICODE)) 'RowNumber', M.ICODE, M.ITEM, M.MONTHS, M.YEARS, M.GRN, M.MRS, M.MRN, M.STOCK ,
(SELECT SUM((ISNULL(STOCK,0.0)+ISNULL(GRN,0.0)- ISNULL(MRS,0.0)+ ISNULL(MRN,0.0)))
FROM @Pindex WHERE ID<=M.ID AND ICODE=M.ICODE)
FROM @Pindex M
ORDER BY M.ICODE
Regards,
GVPrabu
Regards,
GVPrabu
DECLARE @pind TABLE (ID INT IDENTITY(1,1),ICODE INT, ITEM VARCHAR(100),
MONTHS SMALLINT,YEARS SMALLINT,GRN NUMERIC(10,3),MRS NUMERIC(10,3),
MRN NUMERIC(10,3), STOCK NUMERIC(10,3))
INSERT INTO @Pind (ICODE, ITEM, MONTHS, YEARS, GRN, MRS, MRN, STOCK)
SELECT 179,'BRAKE CHAMBER',4,2010,2,NULL,1,3 UNION ALL
SELECT 179,'BRAKE CHAMBER',4,2010,20,NULL,1,21 UNION ALL
SELECT 179,'BRAKE CHAMBER',4,2010,32,NULL,1,33
SELECT M.ICODE, M.ITEM, M.MONTHS, M.YEARS, M.GRN, M.MRS, M.MRN, M.STOCK ,
(SELECT SUM((ISNULL(GRN,0.0)- ISNULL(MRS,0.0)+ ISNULL(MRN,0.0)))
FROM @Pind WHERE ID<=M.ID AND ICODE=M.ICODE) as CLOSEB
FROM @Pind M这篇关于在SQL Server中，第一行最后一列是如何添加第二行列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！