带有declare的sql视图

Question

我需要在视图中使用它，但它似乎不能使用声明。帮助?

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

Answer 1

出于性能原因，我倾向于在值中使用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;

我还建议您对表别名使用表缩写。

顺便说一句，您可能可以用窗口函数替换逻辑：

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;

这并不是100%保证的，因为join可能正在进行一些过滤。但它很可能会有效地解决你的问题。

Answer 2

您可以创建一个Table Valued函数，然后将查询放入其中，并在视图中选择它，

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

然后在您的视图中：

SELECT * FROM FUNCTION_NAME()

Answer 3

你不能直接修改你的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