我有一个表('names'),其中包括与依赖于id的其他表中的其他数据相关的数据。例如:
*名称表
id | name | predecessor | successor | house | birthplace
-----------------------------------------------------------------
10 Bayezid II 9 11 4 NULL
11 Selim I 10 12 4 5
12 Suleiman 11 13 4 61
*房屋表
id | house
--------------
4 House of Osman
*地方表
id | place
--------------
5 Amasya
61 Trabzon
我想要完成的是构建一个查询,根据id返回整个信息,如:
{"result":[{
"id":"11",
"name":"Selim I",
"predecessor": "Bayezid II",
"successor": "Suleiman",
"house":"House of Osman",
"birthplace":"Amasya"
}]}
所以,房子的名字和出生地来自其他表格("房子"、"地方"),而前任和继任者来自同一表格。我需要帮助来构造这个查询。谢谢你。
只是自连接几次,一次获得前身的行(下面的别名n0
),一次获得后继的行(n2
):
SELECT n1.id, n1.name, n0.name AS predecessor, n2.name AS successor
FROM names n1
LEFT JOIN names n0 ON n1.predecessor = n0.id
LEFT JOIN names n2 ON n1.successor = n2.id
SQL server demo
加入得到房子和出生地,留给读者作为练习。
试试这个:
select n.id,
n.name,
n1.name as predecessor,
n2.name as successor,
h.house,
p.place
from names n
inner join names n1 on n.id = n1.predecessor
inner join names n2 on n.id = n2.successor
left join Houses h on n.house = h.id
left join Place p on n.birthplace = p.id
where n.id = 11