SQL 服务器 : select same column 3 times into 1 column without UNION ALL

Question

我有一个名为 placeTable 的表，共有三列:id、parent_id、name，其中包括国家、州和城市表。

城市的parent_ID是州的id，州的parent_ID是国家的id。

我还有另一个名为 cityList 的表指定了城市，其 ID 与 placeTable 的 ID 相同。

我只想选择表 2 cityList 中列出的城市，包括表 1 中的州和国家。例如，表 2 显示悉尼，所以我想获取悉尼这个城市的 id、parent_ID 和名称，及其表 1 中的州 NSW 及其所属国家澳大利亚。

• 表 1:放置表

 id parent_ID name
 -------------------
 1      0     Australia 
 2      0     UK
 33     1     NSW
 34     1     Western Australia     
 55    33     Sydney
 70    34     Perth

• 表2:城市列表

id        name 
-------------------
55      Sydney

我想要的输出:

 id parent_ID name
-------------------
 1      0     Australia 
 33     1     NSW 
 55    33     Sydney

我可以通过以下方式获得我想要的结果，但时间太长，我认为可能还有其他更聪明的方法可用:

Select distinct pt.id,pt.parent_id,pt.name 
from placeTable AS pt
join cityList as cl on cl.id = pt.id
UNION ALL
Select distinct ly2.id,ly2.parent_id,ly2.name 
from placeTable AS pt
join cityList as cl on cl.id = pt.id
join placeTable AS ly2 on pt.parent_id = ly2.id
UNION ALL
Select distinct ly3.id,ly3.parent_id,ly3.name 
from placeTable AS pt
join cityList as cl on cl.id = pt.id
join placeTable AS ly2 on pt.parent_id = ly2.id
join placeTable as ly3 on ly2.parent_id = ly3.id

我在下面尝试过，但这只能给我城市:

Select distinct pt.id,pt.parent_id,pt.name 
from placeTable AS pt
join cityList as cl on cl.id = pt.id
join placeTable AS ly2 on pt.parent_id = ly2.id
join placeTable as ly3 on ly2.parent_id = ly3.id

Answer 1

请试试这个:

            create table #placeTable (ID int, ParentID int, Name varchar(20))
            insert into #placeTable (ID, ParentID, Name)
            values (1,0,'Australia'), (2,0,'UK'), (33,1,'NSW'), (34,1,'Western Australia'),
            (55,33,'Sydney'),(70,34,'Perth');

            create table #cityList (ID int, Name varchar(20))
            insert into #cityList (ID, Name)
            values (55, 'Sydney'), (70, 'Perth');

            WITH Selects AS (
            SELECT p.*, c.ID as 'GroupID'
            FROM    #placeTable p
            INNER JOIN #cityList c on p.ID = c.ID

            UNION ALL
            SELECT  p.*, s.GroupID
            FROM    #placeTable p 
            INNER JOIN Selects s ON p.ID = s.ParentID
            )
            SELECT ID, ParentID, Name FROM Selects ORDER BY GroupID, ID