sql - 递归 SELECT 查询返回任意深度的比率？

Question

第一次尝试递归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 prefix)费率，但不限于${user rate plan ID} ，而是从与 rate_plan.parent_rate_plan_id 层次结构中任意数量的费率计划相关联的那些中选择费率。当 rate_plan.parent_rate_plan_id = NULL 时递归应该触底。

我只想做一个JOIN，但我需要容纳 N 个父子关系，而不仅仅是两个。

这是在 PostgreSQL 9.x 上。我尝试了 WITH RECURSIVE 和 UNION ALL，在每个 SELECT 上加入 rate_plan 到 rate并尝试按父项进行过滤，但由于对这些构造的工作原理了解不足，一无所获。

Answer 1

根据您的描述，这可能是您正在寻找的:

the most-specific (LENGTH()-iest prefix) rate, but not being limited to ${user rate plan ID}, but instead picking rates from those affiliated

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会更有效。

The manual on (recursive) CTEs.