我有以下位置层次结构。作业被分配到位置。如果我只有位置名称,如何返回该位置以及该位置下的任何位置的所有作业?
例如,如果我选择 Leeds
或 Oakwood
则只应返回作业 1 和 2。如果我选择Yorkshire
或England
或Uk
或Europe
,那么所有 3 个作业都将返回。
Locations:
id | name | continent | country | admin1 | admin2 | city
-------------------------------------------------------------------------------------
1 | Europe | | | | |
2 | UK | Europe | | | |
3 | England | Europe | UK | | |
4 | Yorkshire | Europe | UK | England | |
5 | Leeds | Europe | UK | England | Yorkshire |
6 | Oakwood | Europe | UK | England | Yorkshire | Leeds
Jobs:
id | location_id
--------------------
1 | 6
2 | 6
3 | 4
当您知道要按哪一列过滤时,这是直截了当的,例如
Select jobs.*
from jobs
INNER JOIN locations on locations.id = jobs.location_id
where locations.name = 'Europe' OR location.continent = 'Europe'
Select jobs.*
from jobs
INNER JOIN locations on locations.id = jobs.location_id
where locations.name = 'UK' OR location.country = 'UK'
但是,当您不知道要过滤到哪一列中时,您如何实现相同的目标。
case when
表达式:
select jobs.*
from (
select id
from locations
where name = "Europe"
union all
select child.id
from locations main
inner join locations child
on main.name = case when main.continent is null then child.continent
when main.country is null then child.country
when main.admin1 is null then child.admin1
when main.admin2 is null then child.admin2
else child.city
end
where main.name = "Europe"
) sub
inner join jobs
on jobs.location_id = sub.id
您使用的数据库模型存在两个主要问题:
- 具有不规则的层次结构规则。第一行遵循与表其余部分不同的规则。
- 层次结构是硬编码的,最多有 6 个级别。如果某些社区需要更多级别会发生什么?
无论如何,可以检索您拥有的信息,尽管使用丑陋的SQL查询。在这里:
select
j.*
from (
select -- select the initial location
from locations l
where name = 'Yorkshire'
union
select -- select all children locations
from locations l
join locations r
on (l.continent is null and l.name = r.continent)
or (l.continent is not null and l.country is null and l.name = r.country)
or (l.continent is not null and l.country is not null and l.admin1 is null and l.name = r.admin1)
or (l.continent is not null and l.country is not null and l.admin1 is not null and l.admin2 is null and l.name = r.admin2)
or (l.continent is not null and l.country is not null and l.admin1 is not null and l.admin2 is not null and l.city is null and l.name = r.city)
where l.name = 'Yorkshire'
) x
join jobs j on j.location_id = x.id
注意:此查询不使用 CTE(公用表表达式),因此适用于 MySQL 5.x 和 MySQL 8.x。
不改变表格?在定义关系的所有字段上将位置联接到自身。
例:
select loc.name, job.*
from Locations as loc
join Locations as parent_loc
on parent_loc.name in (loc.name, loc.continent, loc.country, loc.admin1, loc.admin2, loc.city)
join Jobs as job on job.location_id = loc.id
where parent_loc.name = 'UK'
但是这个模型并没有真正规范化。 重复名称过多。
对表的 pk 使用外键可能是值得的。
那么更改一些名称就不会成为一个小问题。
例如,就像在这个测试中一样
或者,您可以切换到嵌套集模型。
这不需要递归,就像邻接列表模型那样。
有关这 2 种型号的更多信息,请单击此处