SQL查询实现层级组织自底向上优先的配置继承取值需求
问题背景
基础表结构
- 组织表
Organization(organizationId, parentId, name):存储组织层级关系,parentId关联上级组织的唯一标识organizationId - 配置表
Setting(id, organizationId, settingName, settingValue):存储各组织的个性化配置,格式|a,1|表示settingName=a对应的配置值settingValue=1
业务规则
现有组织层级关系为 A->B->C->D,各组织的配置项如下:
| 所属组织 | 配置项清单 |
|---|---|
| 组织A | a=1、b=2、c=3、d=4 |
| 组织B | b=5 |
| 组织C | a=8 |
| 组织D | c=2 |
查询组织D的生效配置规则:同一个配置项优先取层级中距离D最近的本级/下级配置,不存在对应配置则向上继承父级配置,直到根组织,期望返回结果为 a=8、b=5、c=2、d=4
现有问题
已编写的递归CTE可以返回从D到根组织所有层级的配置,但无法按优先级筛选每个配置项的最终生效值。
解决方案
核心逻辑:你已实现的递归CTE中level值越小,代表对应组织距离查询的目标组织越近、配置优先级越高。我们只需给同一配置项按优先级排序,取每组优先级最高的第一条即可,完整SQL如下:
WITH RECURSIVE cte AS ( SELECT *, 1 AS level FROM Organization -- 此处替换为目标组织D的实际organizationId,你原代码中写的3可按实际值调整 WHERE organizationId = 3 UNION ALL SELECT t.*, cte.level + 1 FROM cte JOIN Organization t ON t.organizationId = cte.parentId ), ranked_settings AS ( SELECT s.settingName, s.settingValue, -- 按配置项分组,层级越近排序越靠前 ROW_NUMBER() OVER (PARTITION BY s.settingName ORDER BY cte.level ASC) AS rn FROM cte INNER JOIN Setting s ON s.organizationId = cte.organizationId ) -- 取每个配置项优先级最高的生效值 SELECT settingName, settingValue FROM ranked_settings WHERE rn = 1;
内容的提问来源于stack exchange,提问作者sliceh
相关产品推荐
相关产品推荐

