sql - MS SQL Server - 如何从 CTE 创建 View ？

Question

with cte as (
    select '2014-03-10 08:00:00' as Dates
    union all
    select '2014-05-11 14:00:00'
)
select * from cte 
join someTable on 1=1 
OPTION (MAXRECURSION 0)

上面的 SQL 像魅力一样输出两个日期之间的所有时间以及从与另一个表的联接检索的字段:

2014-03-10 02:00:00    A
2014-03-10 02:00:00    B
2014-03-10 03:00:00    A
2014-03-10 03:00:00    B
...
2014-05-11 13:00:00    A
2014-05-11 13:00:00    B
2014-05-11 14:00:00    A
2014-05-11 14:00:00    B

我想从中创建一个 View ，但我无法做到这一点。我尝试了几件事但没有成功。返回以下内容:关键字“OPTION”附近的语法不正确。

CREATE VIEW viewName as 
with cte as (
    select '2014-03-10 08:00:00' as Dates
    union all
    select '2014-05-11 14:00:00'
)
select * from cte 
join someTable on 1=1 
OPTION (MAXRECURSION 0)

Answer 1

您无法在 View 内指定 MAXRECURSION 选项。

来自http://benchmarkitconsulting.com/colin-stasiuk/2010/04/12/maxrecursion-with-a-cte-in-a-view/ :

为了使用 MAXRECURSION 选项，您需要首先创建 View 而不使用 MAXRECURSION 选项:

USE AdventureWorks;GOCREATE VIEW vwCTE AS--Creates an infinite loopWITH cte (EmployeeID, ManagerID, Title) as(    SELECT EmployeeID, ManagerID, Title    FROM HumanResources.Employee    WHERE ManagerID IS NOT NULL  UNION ALL    SELECT cte.EmployeeID, cte.ManagerID, cte.Title    FROM cte    JOIN  HumanResources.Employee AS e        ON cte.ManagerID = e.EmployeeID)-- Notice the MAXRECURSION option is removedSELECT EmployeeID, ManagerID, TitleFROM cteGO

然后，当您查询 View 时，包括 MAXRECURSION 选项:

USE AdventureWorks;GOSELECT  EmployeeID, ManagerID, TitleFROM    vwCTEOPTION (MAXRECURSION 2);

另请参阅 AaskashM 的回答 https://stackoverflow.com/a/7428903/195687