如何在视图中实现CTE

问题描述

我有CTE以下，它正确执行，我想在视图中实现这个CTE，我试过但结果并不像我预期的那样。



这个是CTE：

DECLARE @levelId BIGINT;

SET @levelId = 7;

with tblChild AS

（

SELECT *

FROM TBL_EQUIPMENT WHERE levels = @levelId

UNION ALL

SELECT TBL_EQUIPMENT。* FROM TBL_EQUIPMENT加入tblChild ON TBL_EQUIPMENT.levels = tblChild.equipID

）

SELECT *

来自tblChild

选项（MAXRECURSION） 32767）



我尝试过的事情：



我创造了一个类似的视图这个：

创建视图V_AllSubEquipment

AS

with tblChild AS

（

SELECT *

FROM TBL_EQUIPMENT WHERE levels = levels

UNION ALL

SELECT TBL_EQUIPMENT。* FROM TBL_EQUIPMENT JOIN tblChild ON TBL_EQUIPMENT。 levels = tblChild.equipID

）

SELECT *

FROM tblChild

解决方案

I have below CTE, it executes properly, i want to implement this CTE in view, i tried but the result was not as what i expected.

This is the CTE :
DECLARE @levelId BIGINT;
SET @levelId = 7;
WITH tblChild AS
(
SELECT *
FROM TBL_EQUIPMENT WHERE levels = @levelId
UNION ALL
SELECT TBL_EQUIPMENT.* FROM TBL_EQUIPMENT JOIN tblChild ON TBL_EQUIPMENT.levels = tblChild.equipID
)
SELECT *
FROM tblChild
OPTION(MAXRECURSION 32767)

What I have tried:

I CREATED VIEW LIKE THIS :
CREATE VIEW V_AllSubEquipment
AS
WITH tblChild AS
(
SELECT *
FROM TBL_EQUIPMENT WHERE levels = levels
UNION ALL
SELECT TBL_EQUIPMENT.* FROM TBL_EQUIPMENT JOIN tblChild ON TBL_EQUIPMENT.levels = tblChild.equipID
)
SELECT *
FROM tblChild

解决方案

这篇关于如何在视图中实现CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！