MS SQL Server语言 - 如何从 CTE 创建视图

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

我想从中创建一个视图，但我无法做到。我尝试了几件事，但没有成功。以下返回： 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)

Answer 1

不能在视图中指定MAXRECURSION选项。

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

为了使用 MAXRECURSION 选项，

您需要首先创建视图而不使用 MAXRECURSION 选项：

使用冒险作品;去创建视图 VWCTE 作为--创建无限循环使用 cte（员工 ID、经理 ID、头衔）作为(    选择员工 ID、经理 ID、职务    来自人力资源.员工    其中管理器 ID 不为空  全部联盟    选择 CTE。员工 ID，cte。经理ID，cte。标题    来自 CTE    加入人力资源.员工作为 e        在 CTE.经理 ID = e.员工ID)-- 请注意，删除了 MAXRECURSION 选项选择员工 ID、经理 ID、职务来自 CTE去

然后，当您查询视图时，包括 MAXRECURSION 选项：

使用冒险作品;去选择员工 ID、经理 ID、职务从大众科技选项（最大递归 2）;

另见AaskashM在 https://stackoverflow.com/a/7428903/195687 的回答

Answer 2

如果您有超过 100 个预期结果，并且希望避免将 OPTION 语句添加到 VIEW 调用中，请尝试在 VIEW 中的 OPENQUERY 语句中执行 CTE 查询（包括 OPTION 子句）。

在您的示例中，它可能看起来像这样：

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

请注意，您必须完全限定对象引用，即数据库和用户规范必须在对象（表、视图、sproc 或函数）引用前面添加前缀。

当然，它有点丑陋，但可以很好地完成工作，并且避免了添加讨厌的 OPTION 条款。

Answer 3

如果你能限制你需要的小时数，你可以删除递归

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

Answer 4

尝试，

CREATE VIEW vw_your_view
AS
WITH TABLE1(a,b,c) 
as (
SELECT a,b,c from Table 
)

其中a, b, c是表字段。

有好的一天！