将SQLServer查询转换为视图 [英] Convert SQLServer query to view

问题描述

先生，我有这些代码可以正常工作

Sir I have these codes that work fine

SELECT Date, DatePart(hour,Date) as Hr, reading
FROM bin1 A
WHERE Date IN 

(SELECT Min(Date) as date
 FROM bin1 B
 WHERE DatePart(hour,A.Date)= DatePart(hour,B.Date) 
 
UNION

 SELECT Min(Date)as date
 FROM bin1_empty C
 WHERE DatePart(hour,A.Date)= DatePart(hour,C.Date)) 

现在我想从这些代码创建一个视图。

Now I want to create a view from these codes.

当我在视图面板中放置这些代码时，它会显示此错误消息

When I put these codes in view panel then it shows this error message

无法解析查询文本。

如何使用上述代码创建物理视图？

How could I create a physical view with above codes?

请帮助

tqmd

推荐答案

嗨tqmd，

Hi tqmd,

正如你所说'当我把这些代码放在视图面板中然后显示此错误信息'时，你在做什么操作？请你分享一下我们的更多信息？

As you said that 'When I put these codes in view panel then it shows this error message', what operation are you doing ? Could you please share us more information ?

 

如果你想创建一个视图 ， 请尝试关注
脚本，我可以成功测试。你的原始描述，我认为它是一个不完整的脚本。所以我创建表并插入一些值。

 

 ---drop table bin1
 create table bin1 
 ([Date] datetime,
 reading varchar(10))
 insert into bin1 values 
 ('20170201','U'),('20180115','U'),('20190212','U')
  ---drop table bin1_empty
 create table bin1_empty 
 (Date datetime )
 insert into bin1_empty values 
('20180115'),('20190214'),('20180525')


 go
 create view dbo.v_viewbin1   
 as
 SELECT [Date], DatePart(hour,[Date]) as Hr, reading
FROM bin1 A
WHERE [Date] IN 

(SELECT Min([Date]) as date
 FROM bin1 B
 WHERE DatePart(hour,A.[Date])= DatePart(hour,B.[Date]) 
 
UNION

 SELECT Min([Date])as date
 FROM bin1_empty C
 WHERE DatePart(hour,A.[Date])= DatePart(hour,C.[Date])) 

 select * from dbo.v_viewbin1   
 /*
 Date                    Hr          reading
----------------------- ----------- ----------
2017-02-01 00:00:00.000 0           U
2018-01-15 00:00:00.000 0           U
 */

希望它可以帮到你。

Hope it can help you.

 

最好的问候，

Rachel

这篇关于将SQLServer查询转换为视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！