将SQLServer查询转换为视图 [英] Convert SQLServer query to view
本文介绍了将SQLServer查询转换为视图的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
先生,我有这些代码可以正常工作
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屋!
查看全文