代码之家 › 专栏 › 技术社区 › Scott Deerwester

SQL选择三级树中符合条件的所有节点

postgresql

Scott Deerwester · 技术社区 · 7 年前

我有一个PostgreSQL模型(在Django的上下文中生成),它看起来像这样:

CREATE TABLE org (
  id INTEGER NOT NULL,
  parent_id INTEGER,
  name CHARACTER VARYING(24),
  org_type  CHARACTER VARYING(8),
  country CHARACTER VARYING(2)
)

CREATE TABLE rate (
  id INTEGER NOT NULL,
  org_id INTEGER NOT NULL,
  rate DOUBLE PRECISION NOT NULL,
  currency CHARACTER VARYING(3)
)

其中,组织类型为“集团”、“公司”和“分支机构”之一。每个分支机构都有一个公司,只有公司属于一个集团。给定一个任意的公司或分支机构,以及一个国家,我需要找到其组织ID是属于同一个集团且在指定国家的公司和分支机构的所有费率。因此,在下图中,对于公司123(在加拿大)或分支机构124(在多伦多),使用country=“us”搜索将在标记为“selected”的框中找到属于公司或分支机构的费率:

我在为公司尝试类似以下的方法, $1 是国家代码和 $2 是一个ORG ID:

SELECT rate.org_id, rate.rate, rate.currency
FROM rate, org
WHERE (
  org.country = $1 AND
  rate.org_id=org.id AND
  org.parent_id = $2
) OR (
  ...

然后我陷入了困境,试图弄清楚如何去寻找属于我刚刚发现的一家公司的分支机构。我真的更喜欢一个大的where条款任何相关的组织,这样我就不必用一大堆查询来锤击数据库了。

编辑

基于 lau's answer ,我试过一个例子( SQL fiddle 但这只是我刚开始的组织的回报率。

2 回复 | 直到 7 年前

FXD 7 年前

你可以:

将条件应用于 org 就像你一样
一路浏览上下使用你的首字母 org (s)作为起点
将这两组与 UNION
JOIN 有费率(低于我做过的 LEFT OUTER JOIN 以明确递归中到底包含了什么)。

例子:

WITH RECURSIVE SelectedOrg AS (
    SELECT * FROM org WHERE id = 4
),
BrowseOrg AS (
    SELECT 1 AS Direction, * FROM SelectedOrg
    UNION ALL
    SELECT -1, * FROM SelectedOrg
    UNION ALL
    SELECT Direction, org.* FROM org JOIN BrowseOrg ON (direction = 1 and org.parent_id = BrowseOrg.id) OR (direction = -1 and org.id = BrowseOrg.parent_id)
)
SELECT DISTINCT rates.id, BrowseOrg.id, rates.rate FROM BrowseOrg LEFT OUTER JOIN rates ON org_id = BrowseOrg.id

这将足够灵活地处理您不知道级别的情况 org 您已选择(组、公司或分支机构)。
在将来,这还应该能够处理更深层的层次结构(如果您曾经向它添加级别的话)。

Gurpreet Singh 7 年前

您可以使用一些子查询在2个查询中完成此操作。

让 company_org_ids = select id from org where country code = $1 and parent_id = $2

Select distinct rate.* from rate where orgid in ($3) or where orgid in (select id from org where parent_id in $3 and country_code =$1)

这里是3美元。 company_org_ids 第一个查询的结果。

此外,还可以通过替换单个查询来完成此操作。 $3 第一个查询的变量。