MSSQL -使用分层查询结果作为子查询

Question

我想使用层次查询结果作为子查询，但是我得到了一些错误:分层查询：

   select * from (WITH tree (Oid, name, level) AS ( SELECT Oid, Name, 1 as level FROM Company 
                where Oid='20C269DE-A15F-48B3-AFC2-47FBF53C1EAE' and GCRecord is null 
                UNION ALL SELECT child.Oid, child.name, parent.level + 1 FROM Company as child 
                JOIN tree parent on parent.Oid = child.ParentCompany where child.GCRecord is null ) SELECT Oid FROM tree) t

我的问题查询如下：

select  *from DeliverySchedule where Company in ( 
WITH tree (Oid, name, level) AS ( SELECT Oid, Name, 1 as level FROM Company 
                where Oid='20C269DE-A15F-48B3-AFC2-47FBF53C1EAE' and GCRecord is null 
                UNION ALL SELECT child.Oid, child.name, parent.level + 1 FROM Company as child 
                JOIN tree parent on parent.Oid = child.ParentCompany where child.GCRecord is null ) SELECT Oid FROM tree)

Answer 1

试一试：

;with tree ( Oid , name , level ) as (
  select Oid
    , name
    , 1 as level
  from Company
  where Oid = '20C269DE-A15F-48B3-AFC2-47FBF53C1EAE' 
    and GCRecord is null
 union all
 select 
      child.Oid
    , child.name
    , parent.level + 1
  from Company as child
    inner join tree parent 
      on parent.Oid = child.ParentCompany
  where child.GCRecord is null
)

select * 
from DeliverySchedule 
where Company in (select Oid from tree);