需要对T-SQL

Question

树型表的查询我有3个表即是这样的关系：

树被加入本身使用的ParentId为外键。树和所有者表通过xrefOwnerTree表具有多对多关系。

我想写一个查询/函数，我给它一个树ID，它给了我一个最近在层次结构（向上）的OwnerId。

这是我到目前为止有：

WITH c (TreeId, Parentid, level, BranchName, OwnerId) as 
(
    SELECT t.TreeId, t.Parentid, 0 as level, BranchName, o.OwnerId 
    FROM Tree t 
    JOIN xrefOwnerTree ot ON t.TreeID = ot.TreeId 
    JOIN Owner o ON ot.OwnerId = o.OwnerId 
    WHERE Parentid is null 

    UNION ALL 

    SELECT t2.TreeId, t2.parentid, c.level + 1, t2.BranchName, o2.OwnerId 
    FROM Tree t2 
    JOIN xrefOwnerTree ot2 ON t2.TreeID = ot2.TreeId 
    JOIN Owner o2 ON ot2.OwnerId = o2.OwnerId 
    INNER JOIN c ON c.TreeId = t2.parentid 
) 
SELECT * FROM t WHERE t.TreeId = 32800 and t.OwnerId is not NULL 

它返回0的记录。它应该返回回1.

样本数据：

select * from tree where treeid = 32800 
union 
select * from tree where treeid = 32646 
union 
select * from tree where treeid = 32645 
union 
select * from tree where treeid = 32619 
union 
select * from tree where treeid = 31459 
union 
select * from tree where treeid = 31458 

select * from owner 

select * from dbo.xrefOwnerTree where treeid = 31459 

WITH c (TreeId, Parentid, level, BranchName, OwnerId) as 
(
    SELECT t.TreeId, t.Parentid, 0 as level, BranchName, o.OwnerId 
    FROM Tree t 
    JOIN xrefOwnerTree ot ON t.TreeID = ot.TreeId 
    JOIN Owner o ON ot.OwnerId = o.OwnerId 
    WHERE Parentid is null 

    UNION ALL 

    SELECT t2.TreeId, t2.parentid, c.level + 1, t2.BranchName, o2.OwnerId 
    FROM Tree t2 
    JOIN xrefOwnerTree ot2 ON t2.TreeID = ot2.TreeId 
    JOIN Owner o2 ON ot2.OwnerId = o2.OwnerId 
    INNER JOIN c ON c.TreeId = t2.parentid 
) 
SELECT * FROM c 

SELECT t.TreeId, t.Parentid, 0 as level, BranchName, o.OwnerId 
FROM Tree t 
JOIN xrefOwnerTree ot ON t.TreeID = ot.TreeId 
JOIN Owner o ON ot.OwnerId = o.OwnerId 
WHERE Parentid is null 

Answer 1

我没有自己的表。我建议你有一个像这样的检查您的联盟之一：

首先检查它：

SELECT t.TreeId, t.Parentid, 0 as level, BranchName, o.OwnerId 
    FROM Tree t 
    JOIN xrefOwnerTree ot ON t.TreeID = ot.TreeId 
    JOIN Owner o ON ot.OwnerId = o.OwnerId 
    WHERE Parentid = 0 

然后第二个..

希望它可以帮助。