MS SQL Server - 如何从 CTE 创建视图? [英] MS SQL Server - How to create a view from a CTE?

问题描述

with cte as (选择2014-03-10 08:00:00"作为日期联合所有选择2014-05-11 14:00:00")从 cte 中选择 *在 1=1 上加入 someTable选项(最大递归 0)

上面的 SQL 在两个日期和从与另一个表的连接中检索的字段之间的所有小时都像魅力一样输出:

2014-03-10 02:00:00 A2014-03-10 02:00:00 乙2014-03-10 03:00:002014-03-10 03:00:00 乙...2014-05-11 13:00:002014-05-11 13:00:00 乙2014-05-11 14:00:002014-05-11 14:00:00 乙

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

CREATE VIEW viewName as与 cte 为 (选择2014-03-10 08:00:00"作为日期联合所有选择2014-05-11 14:00:00")从 cte 中选择 *在 1=1 上加入 someTable选项(最大递归 0)

解决方案

您不能在视图中指定 MAXRECURSION 选项.

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

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

使用 AdventureWorks；走创建视图 vwCTE AS--创建无限循环WITH cte (EmployeeID, ManagerID, Title) 作为(SELECT EmployeeID, ManagerID, TitleFROM HumanResources.Employee其中 ManagerID 不为空联合所有选择 cte.EmployeeID、cte.ManagerID、cte.Title发件人JOIN HumanResources.Employee AS eON cte.ManagerID = e.EmployeeID)-- 注意 MAXRECURSION 选项已删除SELECT EmployeeID, ManagerID, Title发件人走

然后当您查询视图时包含 MAXRECURSION 选项:

<前>使用 AdventureWorks；走SELECT EmployeeID, ManagerID, Title来自 vwCTE选项 (MAXRECURSION 2);

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

上的回答

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:

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'.

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)

解决方案

You cannot specify the MAXRECURSION option inside a view.

From 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:

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:

USE AdventureWorks;
GO
SELECT  EmployeeID, ManagerID, Title
FROM    vwCTE
OPTION (MAXRECURSION 2);

See also AaskashM's answer at https://stackoverflow.com/a/7428903/195687

这篇关于MS SQL Server - 如何从 CTE 创建视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！