如何为此查询创建视图表？

本文介绍了如何为此查询创建视图表？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

晚上好好每个人 域名：SQL Server 查询：

Hi, Good Evening everybody Domain: SQL Server Query:

DECLARE @pind TABLE (ID INT IDENTITY(1,1),ICODE INT, ITEM VARCHAR(100), MONTHS SMALLINT,YEARS SMALLINT,OPEN_BALANCE NUMERIC(10,3),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,32,NULL,1,33 UNION ALL SELECT 179,'BRAKE CHAMBER',7,2010,10,NULL,1,11 UNION ALL SELECT 179,'BRAKE CHAMBER',8,2010,12,NULL,1,13 SELECT M.ID,(ROW_NUMBER() OVER(PARTITION BY M.ICODE,months ORDER BY M.ICODE,months)) 'RowNumber', M.ICODE, M.ITEM, M.MONTHS, M.YEARS,M.OPEN_BALANCE, 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 CLOSE_BALANCE FROM @Pind as M

在这里，我添加了一个名为Open_Balance的新列。现在我想要要在''open_balance''的第一列初始化零，那么第一行''Close_Balance''值将会到达第二行''open_balance''列。怎么可能？请解决此问题..然后最终为此查询创建视图表.. 感谢&问候 Karthick

"Here,I add a new column that name is "Open_Balance".now i want to initialize zero on first column in ''open_balance'' then the first row ''Close_Balance'' value will be come to second row ''open_balance'' column..How is it possible? pls solve this problem.. then finally create view table for this query.. Thank & Regards Karthick

推荐答案

您好， 尝试以下 Hi, Try below DECLARE @pind TABLE (ID INT IDENTITY(1,1),ICODE INT, ITEM VARCHAR(100), MONTHS SMALLINT,YEARS SMALLINT,OPEN_BALANCE NUMERIC(10,3),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,32,NULL,1,33 UNION ALL SELECT 179,'BRAKE CHAMBER',7,2010,10,NULL,1,11 UNION ALL SELECT 179,'BRAKE CHAMBER',8,2010,12,NULL,1,13 update b set OPEN_BALANCE = a.close_balance from @Pind b inner join ( select id,(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 CLOSE_BALANCE FROM @Pind as M ) a on a.ID = b.ID -1 SELECT M.ID,(ROW_NUMBER() OVER(PARTITION BY M.ICODE,months ORDER BY M.ICODE,months)) 'RowNumber', M.ICODE, M.ITEM, M.MONTHS, M.YEARS,M.OPEN_BALANCE, 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 CLOSE_BALANCE FROM @Pind as M

你的q uestion很容易回答，因为你提供了一个小例子，我可以将测试数据直接粘贴到SQL Server Management Express中。我会给你五个。 使用IDENTITY列（ID）的值来做决定不是一个好主意。 /> 我使用Joe Celko的 SQL For Smarties：高级SQL编程一书来举例说明如何进行开仓和平仓。 。 Your question was easy to answer because you gave a small example with test data that I could paste directly into SQL Server Management Express. I''ll give you a five for that. It is not a good idea to use the value of the IDENTITY column (ID) to make decisions. I used Joe Celko''s "SQL For Smarties: Advanced SQL Programming" book for an example on how to do Opening and Closing Balances.. DECLARE @pind TABLE (ID INT IDENTITY(1,1),ICODE INT, ITEM VARCHAR(100), MONTHS SMALLINT,YEARS SMALLINT,OPEN_BALANCE NUMERIC(10,3),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,32,NULL,1,33 UNION ALL SELECT 179,'BRAKE CHAMBER',7,2010,10,NULL,1,11 UNION ALL SELECT 179,'BRAKE CHAMBER',8,2010,12,NULL,1,13 ; -- -- Computes both Opening and Closing Balance -- SELECT M.ICODE, M.ITEM, M.MONTHS, M.YEARS, ISNULL((select SUM((ISNULL(OB.GRN,0.0)- ISNULL(OB.MRS,0.0)+ ISNULL(OB.MRN,0.0))) from @Pind as OB where ((OB.years*100)+OB.months)<((m.years*100)+m.months) AND OB.ICODE=m.ICODE),0) AS [Opening Balance], M.GRN, M.MRS, M.MRN, M.STOCK , (select SUM((ISNULL(CB.GRN,0.0)- ISNULL(CB.MRS,0.0)+ ISNULL(CB.MRN,0.0))) from @Pind as CB where ((CB.years*100)+CB.months)<=((m.years*100)+m.months) AND CB.ICODE=m.ICODE) AS [Closing Balance] FROM @Pind as m

--Computes and sets Opening Balance Update m set OPEN_BALANCE= ISNULL((select SUM((ISNULL(OB.GRN,0.0)- ISNULL(OB.MRS,0.0)+ ISNULL(OB.MRN,0.0))) from @Pind as OB where ((OB.years*100)+OB.months)<((m.years*100)+m.months) AND OB.ICODE=m.ICODE),0) from @PIND as m

ICODE ITEM MONTHS YEARS Opening Balance GRN MRS MRN STOCK Closing Balance 179 BRAKE CHAMBER 4 2010 0.000 32.000 NULL 1.000 33.000 33.000 179 BRAKE CHAMBER 7 2010 33.000 10.000 NULL 1.000 11.000 44.000 179 BRAKE CHAMBER 8 2010 44.000 12.000 NULL 1.000 13.000 57.000

经测试：SQL Server Express 2012 创建视图

Tested: SQL Server Express 2012 To create a View

Create View MyView As SELECT M.ICODE, M.ITEM, M.MONTHS, M.YEARS, ISNULL((select SUM((ISNULL(OB.GRN,0.0)- ISNULL(OB.MRS,0.0)+ ISNULL(OB.MRN,0.0))) from @Pind as OB where ((OB.years*100)+OB.months)<((m.years*100)+m.months) AND OB.ICODE=m.ICODE),0) AS [Opening Balance], M.GRN, M.MRS, M.MRN, M.STOCK , (select SUM((ISNULL(CB.GRN,0.0)- ISNULL(CB.MRS,0.0)+ ISNULL(CB.MRN,0.0))) from @Pind as CB where ((CB.years*100)+CB.months)<=((m.years*100)+m.months) AND CB.ICODE=m.ICODE) AS [Closing Balance] FROM @Pind as m

文档： 创建视图（Transact SQL） [ ^ ]