MS SQL Server-如何使用分层查询创建视图

Question

我想使用with和removing 4 join创建分层查询，有人可以帮助我，我是SQL视图的初学者。

CREATE VIEW CAR( ID, CARLEVEL) AS    
select
t.C_ID,
coalesce(d.C_ID, coalesce(c.C_ID, coalesce(b.C_ID, 
coalesce(a.C_ID, t.C_ID))))
from tablex t LEFT OUTER JOIN tablex a LEFT OUTER JOIN tablex b 
LEFT OUTER JOIN tablex c 
LEFT OUTER JOIN tablex d ON c.Title = d.C_ID ON b.Title = c.C_ID 
ON a.Title = b.C_ID ON t.Title = a.C_ID

Tablex的内容是：

C_ID    Title         
67       Null
68       Null
69       68
70       68
71       68
72       Null
81       Null
82       81
83       81
84       Null
86       Null
87       Null
104       81
105       81
106       81
107       Null
4707      81

我对CTE的期望是：

ID    CAR LEVEL
69     68
70     68
71     68
68     68
82     81
83     81
104    81
105    81
106    81
4707  81
81     81

Answer 1

没有hierarchical engine。有一种层次结构类型hierarchyid可以表示层次结构并加速性能很多，但从评论来看，你似乎不想使用它。

要像这样查询自引用层次结构，可以像以前那样将表连接到特定级别，也可以使用递归公共表表达式。CTE在某种程度上类似于定义"；视图"；在查询本身中。一个重要的区别是CTE可以引用自己，从而创建递归CTE。

本文以分层查询为例，解释了如何使用CTE。

CTE的一部分选择"；根"；行。这被称为anchor，因为这是我们的起点。第二个，递归查询，选择那些与"；先前的"；(锚的(结果

在您的情况下，查询看起来像：

With MyCTE
AS (
--Anchor. Get the roots
SELECT
t.ID,
NULL as ParentID
FROM tablex 
WHERE ParentID is null
UNION ALL
--Recursive. Get the direct descendants of the "previous" case
SELECT 
t.ID,
t.ParentID
FROM tablex t 
INNER JOIN MyCTE m on m.ID=t.ParentID
WHERE t.ParentID is NOT NULL
)
SELECT t.ID as CarID, t.ParentID
FROM MyCTE

为了获得级别，我们可以添加另一个以0或1开头的列，并在递归查询中递增：

With MyCTE
AS (
-- Anchor
SELECT
ID,
ParentID,
1 as Level.           -- Start with Level = 1
FROM tablex 
WHERE ParentID is null
UNION ALL
-- Recursive
SELECT 
t.ID,
t.ParentID,
m.Level+1 as Level.   -- Increment the level
FROM tablex t 
INNER JOIN MyCTE m on m.ID=t.ParentID
WHERE t.ParentID is NOT NULL
)
SELECT 
ID as CarID, 
Level as CarLevel
FROM MyCTE