MySQL - 具有父子关系的递归sql查询

Question

我有以下位置层次结构。工作被分配到地点。如果我只有位置名称，如何返回该位置以及该位置下的任何位置的所有作业？

例如，如果我选择 Leeds 或 Oakwood，则仅应返回作业 1 和 2。如果我选择约克郡或英格兰或英国或欧洲，那么所有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'

但是，当您不知道要过滤到哪一列时，如何才能实现相同的目标。

Answer 1

您可以使用 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