我有以下带有示例数据的mysql表格:
id location parentid
1 UK 0
2 East Anglia 1
3 Cambridgeshire 2
4 Norfolk 2
5 Suffolk 2
6 East Midlands 1
7 Derbyshire 6
8 Leicestershire 6
9 EU Countries 0
10 Austria 9
11 Belgium 9
我希望生成一个查询,通过位置名称获取包括任何父级位置的位置列表。例如,
搜索“folk”应返回:
id location
4 Norfolk, East Anglia, UK
5 Suffolk, East Anglia, UK
搜索关键词
East
应返回以下内容:id location
2 East Anglia, UK
6 East Midlands, UK
搜索“Bel”应该返回以下结果:
id location
11 Belgium
在上述内容中,我们正在排除连接“欧盟国家”。
显然,以下内容不起作用:
select c.id, CONCAT_WS(', ', c.location, p.location, pp.location) as location
from tbl_locations c
outer left join tbl_locations p on p.id = c.parentid
outer left join tbl_locations pp on pp.id = p.parentid
where c.location like '%whatever%'