这是我第一次尝试递归SQL查询以向上遍历N个父子关系,但我不知道从哪里开始。任何帮助,将不胜感激。
场景是我有两个表-rate
和rate_plan
。费率属于应用于用户的费率计划。
CREATE TERM rate_plan (
id integer PRIMARY KEY NOT NULL
DEFAULT nextval('rate_plan_id'),
descr varchar(64) NOT NULL,
parent_rate_plan_id integer NOT NULL REFERENCES rate_plan(id)
);
CREATE TABLE rate (
id integer PRIMARY KEY NOT NULL
DEFAULT nextval('rate_id'),
prefix varchar(24) NOT NULL,
rate_plan_id integer NOT NULL
REFERENCES rate_plan(id)
);
获取费率的典型查询:
SELECT * FROM rate
WHERE (
rate_plan_id = ${user rate plan ID}
AND prefix = ${prefix}
)
ORDER BY LENGTH(prefix) ASC;
我想要的是返回最具体的(LENGTH()
-iest前缀)费率,但不限于${user rate plan ID}
,而是从与rate_plan.parent_rate_plan_id
层次结构中任意数量的费率计划相关的费率中选择费率。递归应该在何时到达最低点rate_plan.parent_rate_plan_id = NULL
。
我只想做一个JOIN
,但我需要容纳N个亲子关系,而不仅仅是两个。
这是在PostgreSQL 9.x上。我尝试了WITH RECURSIVE
and UNION ALL
,加入rate_plan
了rate
每种方法,SELECT
并尝试按父级进行过滤,但由于对这些构造如何工作的理解不足,因此一无所获。
根据您的描述,这可能是您要寻找的:
最具体的(
LENGTH()
-iest前缀)费率,但不仅限于${user rate plan ID}
,而是从关联的费用中选择费率
WITH RECURSIVE cte AS (
SELECT id, parent_rate_plan_id
FROM rate_plan
WHERE id = ${user rate plan ID}
UNION ALL
SELECT rp.id, rp.parent_rate_plan_id
FROM cte
JOIN rate_plan rp ON rp.id = cte.parent_rate_plan_id
)
SELECT *
FROM cte
JOIN rate r ON r.rate_plan_id = cte.id
ODER BY length(prefix) DESC
LIMIT 1;
parent_rate_plan_id IS NULL
到达顶部节点()后,递归将自动停止。
rate
收集所有计划后加入一次会更有效。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句