在SQL Server中，第一行最后一列是如何添加第二行列？ [英] IN SQL Server, First row Last Column is How to add with second row Columns?

问题描述

你好先生，



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..

推荐答案

嗨朋友，



检查我的解决方案......在你的表中，你有唯一的ID列表示使用以下解决方案。

否则使用ID创建临时表（IDENTITY）列并使用相同的解决方案。

Hi Friend,

Check my Solution... In your Table, U have Unique ID Column means Use the Following Solution.
Else Create Temp Table with ID (IDENTITY) Column and Use the Same Solution.

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中，第一行最后一列是如何添加第二行列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！