时间戳

首页 > 解决方案 > MS SQL Server - 如何使用分层查询创建视图

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

问题描述

我想使用 with 和删除 4 连接创建分层查询，有人可以帮助我是 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

标签： sqlsql-servercommon-table-expressioncreate-viewhierarchical-query

解决方案

没有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

推荐阅读