MS SQL Server - 如何从 CTE 创建视图？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27172801/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MS SQL Server - How to create a view from a CTE?
提问by Marc
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)
The here above SQL is outputing like a charm all hours between two dates and a field retrieved from a join with another table:
上面的 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
I would like to create a view from that but I do not manage to do it. I tried several things but without success. The following is returning : Incorrect syntax near the keyword 'OPTION'.
我想从中创建一个视图，但我无法做到。我尝试了几件事，但没有成功。以下是返回：Incorrect syntax near the keyword '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)
回答by vstrien
You cannot specify the MAXRECURSIONoption inside a view.
您不能MAXRECURSION在视图内指定选项。
From http://benchmarkitconsulting.com/colin-stasiuk/2010/04/12/maxrecursion-with-a-cte-in-a-view/:
从http://benchmarkitconsulting.com/colin-stasiuk/2010/04/12/maxrecursion-with-a-cte-in-a-view/：
In order to make use of the MAXRECURSION option you need to first create your view without using the MAXRECURSION option:
为了使用 MAXRECURSION 选项，您需要首先创建视图而不使用 MAXRECURSION 选项：
USE AdventureWorks;
GO
CREATE VIEW vwCTE AS
--Creates an infinite loop
WITH 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 removed
SELECT EmployeeID, ManagerID, Title
FROM cte
GO
Then when you query the view include the MAXRECURSION option:
然后在查询视图时包含 MAXRECURSION 选项：
USE AdventureWorks; GO SELECT EmployeeID, ManagerID, Title FROM vwCTE OPTION (MAXRECURSION 2);
See also AaskashM's answer at https://stackoverflow.com/a/7428903/195687
另见 AaskashM 的回答https://stackoverflow.com/a/7428903/195687
回答by jskipb
If you have more than 100 expected results, and want to avoid having to add the OPTION statement to your VIEW calls, try executing the CTE query - including the OPTION clause - in an OPENQUERY statement within your VIEW.
如果您有超过 100 个预期结果，并且想要避免将 OPTION 语句添加到 VIEW 调用中，请尝试在 VIEW 内的 OPENQUERY 语句中执行 CTE 查询 - 包括 OPTION 子句。
In your example, it would probably look something like this:
在您的示例中，它可能看起来像这样：
USE AdventureWorks;
GO
CREATE VIEW vwCTE AS
select * from OPENQUERY([YourDatabaseServer], '
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
SELECT EmployeeID, ManagerID, Title
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID IS NOT NULL
UNION ALL
SELECT cte.EmployeeID, cte.ManagerID, cte.Title
FROM cte
JOIN AdventureWorks.HumanResources.Employee AS e
ON cte.ManagerID = e.EmployeeID
)
-- Notice the MAXRECURSION option is removed
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 0)
' ) x
GO
Notice that you must fully-qualify object references, i.e. the database and user specifications must prefix the object (table, view, sproc, or function) references.
请注意，您必须完全限定对象引用，即数据库和用户规范必须作为对象（表、视图、sproc 或函数）引用的前缀。
Sure, it's a little ugly, but gets the job done nicely, and avoids having to add that pesky OPTION clause.
当然，它有点难看，但是很好地完成了工作，并且避免了添加那个讨厌的 OPTION 子句。
回答by adrianm
If you can put a limit on the number of hours you need you can remove the recursion
如果您可以限制所需的小时数，则可以删除递归
CREATE VIEW viewName AS
WITH TenNumbers AS (
SELECT Number
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS T(Number)
)
,Numbers AS (
SELECT ROW_NUMBER() OVER (ORDER BY T10.Number) AS Number
FROM TenNumbers AS T10
CROSS JOIN TenNumbers AS T100
CROSS JOIN TenNumbers AS T1000
-- ...
)
SELECT DATEADD(hour, Number - 1, '20140310 08:00:00') AS Dates
FROM Numbers
