如何将以前的列输出作为当前列输入? [英] How to get a previous column output as current column input?
问题描述
我有一张4列的桌子如下
Sno Opening Plusvalue MinusValue关闭
1 0 5 2 0
2 0 8 4 0
3 0 8 6 0
4 0 9 7 0
到达我的结束栏的公式是
收盘=开盘+加值 - 减去价值
到达我的开场栏的公式是
开盘=上一栏收盘
我要求的输出是
Sno开盘加值减去价值结束
1 0 5 2 3 >
2 3 8 4 7
3 7 8 6 9
4 9 9 7 11
我需要一个查询来实现此输出
您好Krithiga,
查看以下示例TSQL脚本。
- 表创建语句
IF OBJECT_ID (' TempDB ..#TestSample') IS NOT NULL DROP 表 #TestSample
CREATE TABLE #TestSample(Sno INT ,打开 INT ,Plusvalue INT ,MinusValue INT ,关闭 INT )
- 插入样本值
INSERT INTO #TestSample(Sno,Opening,Plusvalue,MinusValue,Closing)
VALUES ( 1 , 0 , 5 , 2 , 0 ),( 2 , 0 , 8 , 4 , 0 ),( 3 , 0 , 8 , 6 , 0 ),( 4 , 0 , 9 , 7 , 0 )
- 给定数据
SELECT Sno,Openi ng,Plusvalue,MinusValue,结束 FROM #TestSample
- 必需输出
SELECT T.Sno,(ISNULL(( SELECT SUM((Opening + Plusvalue-MinusValue))
FROM #TestSample
WHERE Sno< T.Sno), 0 ))' 打开',
T.Plusvalue,T.MinusValue,
(ISNULL(( SELECT SUM((Opening + Plusvalue-MinusValue))
FROM #TestSample
WHERE Sno< T.Sno), 0 )+ T.Plusvalue - T.MinusValue)' 关闭'
FROM #TestSample T
问候,
GVPrabu
嗨Prabhu,
如果我有一些内部分组如何进行相同操作。
例如
IF OBJECT_ID (' TempDB ..#TestSample')< span class =code-keyword> IS NOT NULL DROP 表 #TestSample
CREATE TABLE #TestSample(Sno INT ,Group1 INT ,打开 INT ,Plusvalue INT ,MinusValue INT ,结束< span class =code-keyword> INT )
INSERT INTO #TestSample(Sno,Group1,Opening,Plusvalue,MinusValue,Closing)
VALUES ( 1 , 1 , 0 , 5 , 2 , 0 ),( 1 , 2 , 0 , 8 , 4 , 0 ),( 1 , 3 , 0 , 8 , 6 , 0 ),( 2 , 1 , 0 , 9 , 7 , 0 ),( 2 , 2 , 0 , 7 , 2 , 0 )
选择 * 来自 #TestSample
我的要求输出是
Sno Group1开盘加值MinusValue收盘
1 1 0 5 2 3
1 2 3 8 4 7
1 3 7 8 6 9
2 1 0 9 7 2
2 2 2 7 2 7
(即)对于Sno 1单独组和Sno 2单独组。
请告诉我解决方案。
< blockquote>嗨Kirthiga,
试试这个
- 必需输出
SELECT T. Sno,Group1,(ISNULL(( SELECT SUM((Opening + Plusvalue-MinusValue))
FROM #TestSample
WHERE Group1< T.Group1 AND Sno = T.SNo), 0 ))' 打开',
T.Plusvalue,T.MinusValue,
(ISNULL(( SELECT SUM((Opening + Plusvalue-MinusValue))
FROM #TestSample
WHERE Group1< T.Group1 AND Sno = T.SNo), 0 )+ T.Plusvalue - T.MinusValue)' 关闭'
FROM #TestSample T
祝你有愉快的一天..... :-)
问候,
Venkatesh。
Hi,
Im having a table with 4 columns as below
Sno Opening Plusvalue MinusValue Closing
1 0 5 2 0
2 0 8 4 0
3 0 8 6 0
4 0 9 7 0
Formula for arriving my closing column is
Closing = Opening+Plusvalue-MinusValue
Formula for arriving my opening column is
Opening = Previous column closing
My required output is
Sno Opening Plusvalue MinusValue Closing
1 0 5 2 3
2 3 8 4 7
3 7 8 6 9
4 9 9 7 11
I need a query to acheive this output
Hi Krithiga,
Check my following Sample TSQL Script.
-- Table Creation Statement IF OBJECT_ID('TempDB..#TestSample') IS NOT NULL DROP TABLE #TestSample CREATE TABLE #TestSample(Sno INT,Opening INT,Plusvalue INT,MinusValue INT,Closing INT) -- Insert Sample Values INSERT INTO #TestSample(Sno, Opening, Plusvalue, MinusValue, Closing) VALUES(1, 0, 5, 2, 0),(2, 0, 8, 4, 0),(3, 0, 8, 6, 0),(4, 0, 9, 7, 0) -- Given Data SELECT Sno, Opening, Plusvalue, MinusValue, Closing FROM #TestSample -- Required Output SELECT T.Sno, (ISNULL((SELECT SUM((Opening+Plusvalue-MinusValue)) FROM #TestSample WHERE Sno < T.Sno),0)) 'Opening', T.Plusvalue, T.MinusValue, (ISNULL((SELECT SUM((Opening+Plusvalue-MinusValue)) FROM #TestSample WHERE Sno < T.Sno),0) + T.Plusvalue - T.MinusValue) 'Closing' FROM #TestSample T
Regards,
GVPrabu
Hi Prabhu,
If I have some internal grouping how to proceed the same.
For Example
IF OBJECT_ID('TempDB..#TestSample') IS NOT NULL DROP TABLE #TestSample CREATE TABLE #TestSample(Sno INT,Group1 INT,Opening INT,Plusvalue INT,MinusValue INT,Closing INT) INSERT INTO #TestSample(Sno, Group1, Opening, Plusvalue, MinusValue, Closing) VALUES(1, 1, 0, 5, 2, 0),(1, 2, 0, 8, 4, 0),(1, 3, 0, 8, 6, 0),(2, 1, 0, 9, 7, 0),(2, 2, 0, 7, 2, 0) select * from #TestSample
My Required output is
Sno Group1 Opening Plusvalue MinusValue Closing
1 1 0 5 2 3
1 2 3 8 4 7
1 3 7 8 6 9
2 1 0 9 7 2
2 2 2 7 2 7
(ie) For Sno 1 seperate group and for Sno 2 seperate group.
Please tell me the solution.
Hi Kirthiga,
Try This
-- Required Output SELECT T.Sno, Group1, (ISNULL((SELECT SUM((Opening+Plusvalue-MinusValue)) FROM #TestSample WHERE Group1 < T.Group1 AND Sno=T.SNo),0)) 'Opening', T.Plusvalue, T.MinusValue, (ISNULL((SELECT SUM((Opening+Plusvalue-MinusValue)) FROM #TestSample WHERE Group1 < T.Group1 AND Sno=T.SNo),0) + T.Plusvalue - T.MinusValue) 'Closing' FROM #TestSample T
Have a nice day..... :-)
Regards,
Venkatesh.
这篇关于如何将以前的列输出作为当前列输入?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!