如何为此查询创建视图表？ [英] How to create view table for this query?

问题描述

晚上好好每个人



域名：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） [ ^ ]

这篇关于如何为此查询创建视图表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！