递归SELECT查询返回任意深度的速率？ [英] Recursive SELECT query to return rates of arbitrary depth?

问题描述

这是我第一次尝试递归SQL查询以向上遍历N个父子关系，但我不知道从哪里开始。任何帮助将不胜感激。

This is my first time attempting a recursive SQL query to traverse N parent-child relationships upward, and I don't know where to start. Any help would be appreciated.

方案是我有两个表- rate 和 rate_plan 。费率属于适用于用户的费率计划。

Scenario is that I have two tables - rate and rate_plan. Rates belong to a rate plan which is applied to a user.

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 时，递归应该达到最低点。

What I would like is to return the most-specific (LENGTH()-iest prefix) rate, but not being limited to ${user rate plan ID}, but instead picking rates from those affiliated with any number of rate plans in a rate_plan.parent_rate_plan_id hierarchy. The recursion should bottom out when rate_plan.parent_rate_plan_id = NULL.

我只会做一个加入，但我需要容纳N个亲子关系，而不仅仅是两个。

I would just do a JOIN, but I need to accommodate N parent-child relationships, not just two.

这是在PostgreSQL 9.x上。我尝试了具有递归性的和 UNION ALL ，将 rate_plan 加入在每个 SELECT 上尝试 rate 并尝试按父级进行过滤，但由于对这些构造方式的理解不足，因此无济于事

This is on PostgreSQL 9.x. I tried WITH RECURSIVE and UNION ALL, joining rate_plan to rate on every SELECT and trying to filter by parent, but got nowhere, due to an inadequate understanding of how those constructs work.

推荐答案

根据您的描述，这可能就是您想要的：

This might be what you are looking for, according to your description:

最具体（ LENGTH（）-最前缀）的费率，但不限于
$ {用户费率计划ID} ，而是从关联的对象中选择费率

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为NULL

Recursion stops automatically as soon as the top node (parent_rate_plan_id IS NULL) is reached.

收集所有数据后加入费率更为有效计划。

It's more effective to join to rate once after you have collected all plans.

手册（递归）CTE。

这篇关于递归SELECT查询返回任意深度的速率？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！