递归CTE查找所有项目的所有祖先

假设我理解正确，它应该像从叶节点向后递归一样简单（这很容易，因为表Items仅存储叶节点）：

;with AncestryTree as (  select Item, Parent  from Items  where Parent is not null  union all  select Items.Item, t.Parent    from AncestryTree t   join Items on t.Item = Items.Parent )select * from AncestryTreeorder by Item, Parent

SQL Fiddle演示