ıt可以在一个视图中创建一个临时表,选择后把它吗?

I need to alter one view and I want to introduce 2 temporary table before the SELECT.

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

Is this possible? And how can I do it?

这是可能的吗?我该怎么做呢?

ALTER VIEW myView
AS 

SELECT *
INTO #temporary1

SELECT *
INTO #temporary2

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

DROP TABLE #temporary1
DROP TABLE #temporary2

When I attempt this it complains that ALTER VIEW must be the only statement in the batch.

当我尝试此操作时，它会抱怨ALTER VIEW必须是批处理中的唯一语句。

How can I achieve this?

我怎样才能做到这一点呢?

Thanks

谢谢

2 个解决方案

#1

No, a view consists of a single SELECT statement. You cannot create or drop tables in a view.

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

Maybe a common table expression (CTE) can solve your problem. CTEs are temporary result sets that are defined within the execution scope of a single statement and they can be used in views.

也许一个通用表表达式(CTE)可以解决您的问题。cte是在单个语句的执行范围内定义的临时结果集，它们可以在视图中使用。

Example (taken from here) - you can think of the SalesBySalesPerson CTE as a temporary table:

例子(从这里)-你可以把salesby营业员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

#2

You can achive what you are trying to do using a Stored Procedure which returns a query result. Views are not suitable for operations like this one.

您可以使用返回查询结果的存储过程来完成正在尝试的操作。视图不适合像这样的操作。

#1