问题描述
我需要改变一个视图,我想在 SELECT 之前引入 2 个临时表.
I need to alter one view and I want to introduce 2 temporary table before the 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
当我尝试这样做时,它抱怨 ALTER VIEW 必须是批处理中的唯一语句.
When I attempt this it complains that ALTER VIEW must be the only statement in the batch.
我怎样才能做到这一点?
How can I achieve this?
推荐答案
不,视图由单个 SELECT
语句组成.您不能在视图中创建或删除表.
No, a view consists of a single SELECT
statement. You cannot create or drop tables in a view.
也许公用表表达式 (CTE) 可以解决您的问题.CTE 是在单个语句的执行范围内定义的临时结果集,它们可以在视图中使用.
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.
示例(取自此处) - 您可以想到 SalesBySalesPerson
CTE 作为临时表:
Example (taken from here) - you can think of the SalesBySalesPerson
CTE as a temporary table:
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
性能注意事项
这篇关于是否可以在视图中创建一个临时表并在选择后删除它?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!