单表省市级连,查询学校的学生姓名并拼接详细地址(附带表)
select s.id 编号,s.name 学生姓名,s.age 年龄,sch.name 学校名字,concat_ws('-',pro.name,city.name,area.name,sch.address) 详细地址
from student s
left join school sch on s.schid = sch.id
left join locations pro on sch.proid = pro.id
left join locations city on sch.cityid=city.id
left join locations area on sch.areaid = area.id
limit 0,1 ;
学校school表
外键 省proid、市cityid、区areaid分别对应locations表的主键id
位置locations表
省市级联sql语句
select sch.name 学校名字,concat_ws('-',pro.name,city.name,area.name,sch.address) 详细地址
from school sch
left join locations pro on sch.proid = pro.id
left join locations city on sch.cityid=city.id
left join locations area on sch.areaid = area.id