带有声明的 sql 视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24963891/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql view with declare
提问by user3074986
i need this in a view, but its seems that you cant use declare. help?
我需要这个视图，但它似乎你不能使用声明。帮助？
declare @lastsat datetime
set @lastsat =
(select max(fechahoy) from [BigArea].[Thing].[Expanded] where DiaSemana='Saturday')
SELECT a.*,
case
when b.fecha_gestion = a.fechahoy and month(fechahoy)!=month(getdate()) then 1
when a.fechahoy = @lastsat then 1
else 0
end as FinDeMEs
FROM [BigArea].[Thing].[Expanded] a
join [BigArea].[dbo].[fechas_gestion] b
on a.fechahoy = b.fecha
回答by Gordon Linoff
For performance reasons, I would be inclined to joinin the value:
出于性能原因，我倾向于使用join以下值：
select e.*,
(case when g.fecha_gestion = e.fechahoy and month(fechahoy) <> month(getdate()) then 1
when e.fechahoy = m.fechahoy then 1
else 0
end) as FinDeMEs
from [AreaComercial].[LARRA_DOM\Mpollak].[Canales_expandida] e join
[AreaComercial].[dbo].[fechas_gestion] g
on e.fechahoy = g.fecha cross join
(select max(fechahoy) as fechahoy
from [AreaComercial].[LARRA_DOM\Mpollak].[Canales_expandida]
where DiaSemana = 'Saturday'
) m;
I also suggest that you use table abbreviations for table aliases.
我还建议您对表别名使用表缩写。
By the way, you can probably replace the logic with a window function:
顺便说一句，您可能可以用窗口函数替换逻辑：
select e.*,
(case when g.fecha_gestion = e.fechahoy and month(fechahoy) <> month(getdate()) then 1
when e.fechahoy = max(case when e.diasemana = 'Saturday' then e.fechahoy end)
then 1
else 0
end) as FinDeMEs
from [AreaComercial].[LARRA_DOM\Mpollak].[Canales_expandida] e join
[AreaComercial].[dbo].[fechas_gestion] g
on e.fechahoy = g.fecha;
This isn't 100% guaranteed, because the joincould be doing some filtering. But it is likely to solve your problem efficiently.
这不是 100% 保证的，因为join可能会进行一些过滤。但它很可能有效地解决您的问题。
回答by Farrokh
you can create a Table Valued Function, then put your query inside it and in your view select it,
您可以创建一个表值函数，然后将您的查询放入其中并在您的视图中选择它，
CREATE FUNCTION FUNCTION_NAME ( )
RETURNS @retContactInformation TABLE
(
-- YOUR COUMN DEFINATIONS HERE
)
AS
declare @lastsat datetime
set @lastsat =
(select max(fechahoy) from [AreaComercial].[LARRA_DOM\Mpollak].[Canales_expandida] where DiaSemana='Saturday')
SELECT a.*,
case
when b.fecha_gestion = a.fechahoy and month(fechahoy)!=month(getdate()) then 1
when a.fechahoy = @lastsat then 1
else 0
end as FinDeMEs
FROM [AreaComercial].[LARRA_DOM\Mpollak].[Canales_expandida] a
join [AreaComercial].[dbo].[fechas_gestion] b
on a.fechahoy = b.fecha
GO
then in your view :
那么在你看来：
SELECT * FROM FUNCTION_NAME()
回答by TMNT2014
Can't you just change your SQL to avoid the variable declaration?
你不能改变你的 SQL 来避免变量声明吗？
SELECT a.*,
case
when b.fecha_gestion = a.fechahoy and month(fechahoy)!=month(getdate()) then 1
when a.fechahoy = (select max(fechahoy) from [AreaComercial].[LARRA_DOM\Mpollak].[Canales_expandida] where DiaSemana='Saturday')
then 1
else 0
end as FinDeMEs
FROM [AreaComercial].[LARRA_DOM\Mpollak].[Canales_expandida] a
join [AreaComercial].[dbo].[fechas_gestion] b on a.fechahoy = b.fecha
回答by Suhel
If you need parameters, performance & declare statements in your view then the alternative below might be worth considering.
如果您需要参数、性能和声明语句，那么下面的替代方案可能值得考虑。
Another alternative is to wrap the logic from your view in to a stored procedure that creates a table. The PROC could truncate & update or delete & recreate the table. If your table is large you can also create indexes on the table.
另一种替代方法是将视图中的逻辑包装到创建表的存储过程中。PROC 可以截断 & 更新或删除 & 重新创建表。如果您的表很大，您还可以在表上创建索引。
If you need to call the view/table in lots of places, you could wrap it around some logic that updates table if some conditions are met. e.g. only update the table once a day or once every 30min etc.
如果您需要在很多地方调用视图/表，您可以将它包装在一些逻辑上，如果满足某些条件，则更新表。例如，每天或每 30 分钟仅更新一次表格等。
Understandably this could create code overhead because prior to every use of the view you'd need to check if it needs to be updated. But upshot is that yopu
可以理解，这可能会产生代码开销，因为在每次使用视图之前，您都需要检查它是否需要更新。但结果是你
Hope that helps.
希望有帮助。
