在视图中创建临时表并在选择后删除，是否可行？

Question

我需要修改一个视图，并且想在SELECT之前引入两个临时表。

这是否可行？如何实现？

ALTER VIEW myView
AS 

SELECT *
INTO #temporary1

SELECT *
INTO #temporary2

SELECT * FROM #temporary1
UNION ALL 
SELECT * FROM #temporary1

DROP TABLE #temporary1
DROP TABLE #temporary2

我尝试这样做时，它抱怨“ALTER VIEW”必须是批处理中唯一的语句。

我该如何实现？

- pencilCake

---

为什么不将进入每个临时表的选择语句联合起来？ - Christian Wattengård

Answer 1

不，视图由单个SELECT语句组成。您不能在视图中创建或删除表。

也许公共表达式（CTE）可以解决您的问题。 CTE是在单个语句的执行范围内定义的临时结果集，并且它们可以在视图中使用。

例如（取自此处）-您可以将SalesBySalesPerson CTE视为临时表：

CREATE VIEW vSalesStaffQuickStats
AS
  WITH SalesBySalesPerson (SalesPersonID, NumberOfOrders, MostRecentOrderDate)
      AS
      (
            SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
            FROM Sales.SalesOrderHeader
            GROUP BY SalesPersonID
      )
  SELECT E.EmployeeID,
         EmployeeOrders = OS.NumberOfOrders,
         EmployeeLastOrderDate = OS.MostRecentOrderDate,
         E.ManagerID,
         ManagerOrders = OM.NumberOfOrders,
         ManagerLastOrderDate = OM.MostRecentOrderDate
  FROM HumanResources.Employee AS E
  INNER JOIN SalesBySalesPerson AS OS ON E.EmployeeID = OS.SalesPersonID
  LEFT JOIN SalesBySalesPerson AS OM ON E.ManagerID = OM.SalesPersonID
GO

性能考虑

公共表表达式（CTE）和临时表哪个更高效？

- Marek Grzenkowicz

---

问题在于，根据您要做什么，公用表表达式(CTE)会对SELECT中的每条记录进行重新评估。这仍可能导致性能受到严重影响。希望有一种方法让CTE只运行“一次”，并且可以像临时表一样反复使用结果。 - Fandango68

@Fandango68 很好的观点！我刚刚为每个想要深入分析它的人提供了链接https://dev59.com/c3RB5IYBdhLWcg3wNk53#26205087。 - Marek Grzenkowicz

关于视图的好处是性能通常不是问题，因为它们通常在后台运行，所以不需要临时表，因为CTE仍然可以完成工作，只是速度较慢。现在，如果CTE超时并且视图无法刷新，则会遇到另一个问题。在这种情况下，最好在视图中添加一个视图。 - K. Ventura

@Fandango68是正确的，我比较了CTE方法和INNER JOIN方法，使用一个有20k条记录的表，将该表连接到自身以过滤部分重复记录。CTE的速度明显较慢（12秒），而INNER JOIN方法只需4秒。 - Aileron79

Answer 2

您可以使用返回查询结果的存储过程来实现您想要做的事情。 视图不适用于此类操作。

- Emir Akaydın

---

请注意：在存储过程内创建临时表将导致该存储过程重新编译（因为模式更改），并在其中填充数据可能会导致第二次重新编译（因为统计信息更改）。这不是一个很好的方法…… - marc_s

我的回答是关于在这种情况下是否可以使用视图。我并不试图给出最优化的解决方案。我的回答适用于任何包含多个SELECT查询的情况。 - Emir Akaydın

我不明白为什么你的存储过程被重新编译两次就被认为是“不太好的方法”...这有什么问题吗？ - Geoff Griswald

Answer 3

虽然不可能，但是如果您尝试使用CTE，这将是代码：

ALTER VIEW [dbo].[VW_PuntosDeControlDeExpediente]
AS
    WITH TEMP (RefLocal, IdPuntoControl, Descripcion) 
    AS 
    (
        SELECT 
              EX.RefLocal
            , PV.IdPuntoControl
            , PV.Descripcion
        FROM [dbo].[PuntosDeControl] AS PV
        INNER JOIN [dbo].[Vertidos] AS VR ON VR.IdVertido = PV.IdVertido
        INNER JOIN [dbo].[ExpedientesMF] AS MF ON MF.IdExpedienteMF = VR.IdExpedienteMF
        INNER JOIN [dbo].[Expedientes] AS EX ON EX.IdExpediente = MF.IdExpediente
    )
    SELECT 
          Q1.[RefLocal]
        ,    [IdPuntoControl] = ( SELECT MAX(IdPuntoControl) FROM TEMP WHERE [RefLocal] = Q1.[RefLocal] AND [Descripcion] = Q1.[Descripcion] )
        , Q1.[Descripcion]
    FROM TEMP AS Q1
    GROUP BY Q1.[RefLocal], Q1.[Descripcion]
GO

- Ángel Ibáñez

---

WITH TEMP 定义的是一个公共表达式（CTE），而不是临时表。 - Russ

好的，但是功能是一样的。 - Ángel Ibáñez

大多数情况下是这样的，但并非完全如此。例如，如果您试图通过强制创建临时表来引导查询优化器朝着某个方向发展，优化器将按照 CTE 的描述进行操作（通常是一个好事情，但不总是如此）。 - Russ

你能更具体地解释一下吗？谢谢。 - Ángel Ibáñez

@ÁngelIbáñez，这不是OP所问的。我会把你的答案改为像这样的东西：“不，您不能在CTE中这样做....” - tavalendo

Answer 4

尝试创建另一个 SQL 视图而不是临时表，然后在主 SQL 视图中引用它。换句话说，视图内嵌视图。完成主视图的创建后，您可以删除第一个视图。

- K. Ventura

---

你要在哪里/如何删除第一个视图？ - Maestro

Answer 5

如@marek-grzenkowicz所提到的，您可以通过使用CTE来解决这个问题。还有一个不太明显的替代方法。您可以创建一个内部函数，在其中可以使用表变量。

CREATE OR ALTER FUNCTION dbo.myFunc
()
RETURNS @table TABLE
(
  [ID]   INT        NULL
, [Name] VARCHAR(3) NULL
)
BEGIN

  DECLARE @temporary1 TABLE
  (
    [ID]   INT        NULL
  , [Name] VARCHAR(3) NULL
  );

  INSERT INTO @temporary1 (ID, Name)
  VALUES (1, 'Foo')
       , (2, 'Bar');

  DECLARE @temporary2 TABLE
  (
    [ID]   INT        NULL
  , [Name] VARCHAR(3) NULL
  );


  INSERT INTO @temporary2 (ID, Name)
  VALUES (3, 'Foo')
       , (4, 'Bar');

  INSERT INTO @table (ID, Name)
  SELECT *
  FROM @temporary1
  UNION ALL
  SELECT *
  FROM @temporary2;

  RETURN;
END;

接下来，您可以在您的视图中使用这个函数。

CREATE OR ALTER VIEW dbo.myView
AS
  SELECT ID
       , Name
  FROM dbo.myFunc ();

- Ako