mysql - 需要 sql 查询来查找层次结构中最低级别的值

Question

表组织(organizationId, parentId, name) 表设置(id, organizationId, settingName, settingValue)

在 |a,1| 中 : a 是 settingName，1 是 settingValue

Organization A   |a,1|    |b,2|    |c,3|    |d,4|
Organization B            |b,5|
Organization C   |a,8|
Organization D                     |c,2|

A->B->C->D (层次结构) 现在获取组织 D 的查询应该给我 (a,8)(b,5)(c,2)(d,4)

我的查询：

with recursive cte as (
select
    *, 1 as level
from
    Organization
where
    organizationId = 3
union ALL 
select
    t.*, cte.level+1
from
    cte
join Organization t on
    t.OrganizationId = cte.parentId )
-- select
    --  ss.OrganizationId,ss.parentId, ss.settingName,ss.settingValue
-- from 
    select
        cte.OrganizationId,
        cte.ParentId,
        s.settingName ,
        s.settingValue,
        level
    from
        cte
    inner join Settings s on
        s.organizationId = cte.OrganizationId
        -- as ss group by ss.settingName
        

这给了我来自所有组织的设置，但我需要来自父母的最新孩子的价值观（如果有的话）（这应该一直持续到根组织，自下而上的方法）

设置表数据

组织表数据

标签： mysqlparent-childjooqhierarchical-datarecursive-query

Answer 1

跟踪感兴趣的 orgId 和设置级别。按级别查找第一个值

with recursive cte as (
   select  s.*, 1 as level, s.organizationId  as orgId
   from    Settings s 
   where   s.organizationId = 4
   union ALL 
   select   s.*, cte.level+1, cte.orgId
   from cte 
   join    Organization t on t.organizationId = cte.organizationId 
   join    settings s on s.organizationId = t.ParentId  
)
select distinct orgid, settingname, first_value(settingvalue) over(partition by orgId, settingname order by level)
from cte

编辑 组织不能有设置的情况

with recursive cte as (
   select  t.organizationId, s.settingname, s.settingvalue, 1 as level, t.organizationId orgId
   from    Organization t
   left join Settings s on t.organizationId = s.organizationId 
   where   t.organizationId = 5
   
   union ALL 
   
   select   t.ParentId, s.settingname, s.settingvalue, cte.level+1, cte.orgId
   from cte 
   join    Organization t on t.organizationId = cte.organizationId 
   left join    settings s on s.organizationId = t.ParentId  
)
select distinct orgid, settingname, first_value(settingvalue) over(partition by orgId, settingname order by level)
from  cte
where settingName is not null 
order by orgid, settingname;

db<>小提琴