mysql - 如何加入不同的表格，如下所示

Question

我有4张桌子，如下所示



enter image description here







现在我想要city_english，city_english + place_english，city_english + place_english + building_english，city_english + place_english + building_english + building_sub_english

如何通过单个mysql查询获取上述格式的数据

city_english + place_english的意思是，我希望通过连接用逗号分隔的两个字符串来获得单个字符串

Answer 1

select 
   t1.city_english,
   concat(t1.city_english,' ',t2.place_english) as concat__name_1
   concat(t1.city_english,' ',t2.place_english,' ',t3.building_english ) as concat_name_2,
   concat(t1.city_english,' ',t2.place_english,' ',t3.building_english,' ',t4.building_sub_english ) as concat_name_2
from 
   tble_cities as t1 
inner join 
   tble_places as t2 on t1.id = t2.city_id
inner join 
   tble_building as t3 on t2.id = t3.place_id
inner join 
   tble_sub_building as t4 on t3.id = t4.sub_building_id