SQL查询，根据特定条件从单个表中获取一级子级详细信息

Question

我的数据库中的一个表包含如下数据，

                     TBLlocations
 -------------------------------------------------------
 LocationId    LocationName      RegisteredUnder    Type
 --------------------------------------------------------
  LOC100        Location1            0               0
  LOC201        Location2           LOC100           2
  LOC102        Location3           LOC201           1
  LOC302        Location4           LOC201           1
  LOC103        Location5           LOC201           1
  LOC104        Location6           LOC201           1
  LOC105        Location7           LOC104           1
  LOC106        Location8           LOC105           1
  LOC107        Location9           LOC106           1

现在我必须从上表中选择位置，这样我的查询将返回第一级位置，即；考虑到上表，我的查询必须返回位置其类型为"1"，并且应该是类型为"0"的一级子位置。从上表中，位置3到6是一级位置，因此查询应返回以下内容：

---------------
   Location3 
   Location4 
   Location5 
   Location6

我试图加入为"Type"提供条件的同一个表。这是我构建的查询：

Select Distinct t1.LocationId,t1.LocationName,t1.RegisteredUnder from TBLlocations t1
join TBLlocations t2 on t2.RegisteredUnder!=t1.LocationId
where t1.Type='1' and t2.Type='1'
order by t1.RegisteredUnder

上面的查询返回了类型"1"下的所有位置，如下所示：

--------------------------------------------------
LocationId     LocationName        RegisteredUnder
--------------------------------------------------
  LOC102        Location3           LOC201 
  LOC302        Location4           LOC201
  LOC103        Location5           LOC201
  LOC104        Location6           LOC201
  LOC105        Location7           LOC104 
  LOC106        Location8           LOC105
  LOC107        Location9           LOC106  

因此，我需要一个能够返回确切结果的查询。我在查询中唯一可以使用的参数是"Type"，并且始终为"1"。

附言：我使用的是SQL Server 2008。

Answer 1

问题更改后

Declare @a table (LocationId Varchar(100),   LocationName Varchar(100),     RegisteredUnder Varchar(100),   Type int)
Insert into @a  Values('LOC100','Location1','0',0)
Insert into @a  Values('LOC201','Location2','LOC100',2)
Insert into @a  Values('LOC102','Location3','LOC201',1)
Insert into @a  Values('LOC302','Location4','LOC201',1)
Insert into @a  Values('LOC103','Location5','LOC201',1)
Insert into @a  Values('LOC104','Location6','LOC201',1)
Insert into @a  Values('LOC105','Location7','LOC104',1)
Insert into @a  Values('LOC106','Location8','LOC105',1)
Insert into @a  Values('LOC107','Location9','LOC106',1)
;With CTE as
(
Select 0 as level,* from @a where Type=1
UNION ALL
Select   c.Level+1, a.* from @a a
join CTE c on c.LocationId=a.RegisteredUnder and a.Type=1
)     
Select  c1.* from CTE c1
Left Join CTE c2 on c2.LocationId=c1.LocationId and c2.level>0
where c2.LocationId is NULL
order by LEVEL desc,LocationName

问题前的答案改变了

Declare @a table (LocationId Varchar(100),   LocationName Varchar(100),     RegisteredUnder Varchar(100),   Type int)
Insert into @a  Values('LOC100','Location1','0',0)
Insert into @a  Values('LOC201','Location2','LOC100',2)
Insert into @a  Values('LOC102','Location3','LOC201',1)
Insert into @a  Values('LOC302','Location4','LOC201',1)
Insert into @a  Values('LOC103','Location5','LOC201',1)
Insert into @a  Values('LOC104','Location6','LOC201',1)
Insert into @a  Values('LOC105','Location7','LOC104',1)
Insert into @a  Values('LOC106','Location8','LOC105',1)
Insert into @a  Values('LOC107','Location9','LOC106',1)
;With CTE as
(
Select 0 as level,* from @a where RegisteredUnder='LOC201'
UNION ALL
Select   c.Level+1, a.* from @a a
join CTE c on c.RegisteredUnder=a.LocationId
) 
Select DISTINCT * from CTE
where level<2
order by LEVEL desc, LocationName