代码之家 › 专栏 › 技术社区 › dallin

如何创建一个SQL调用,以递归方式将不同的ID和父ID组合到单个列中?

lookup-tables recursive-query sql mysql

dallin · 技术社区 · 6 年前

我有一个 company 引用自身的表,如下所示:

company_id    parent_id
-----------------------
1             (NULL)   
2             1        
3             2             
4             1
5             (NULL)

所以A 公司 可以有一个父级 公司 诸如此类。

我还有一个查找表来连接 customer 他们的亲属 公司 就像这样:

customer_id     company_id
--------------------------
1               1
1               3
1               4
2               3
2               2

我正在尝试创建一个SQL调用,该调用将获取与客户ID相关的不同公司ID的列表。这意味着我不仅需要从查找表中获取一对一关系的公司ID,还需要运行所有父级,如果父级ID不在列表中,则添加父级ID,然后查看父级的父级,添加他们的ID(如果他们不在列表中)等等。

例如,在上面的表格中,如果我试图找到所有公司的ID where customer_id = 2 我希望能回来:

company_id
---------------
1
2
3

注意,它递归地得到1,但它没有列出2两次,因为它只查找不同的ID。

不过,当涉及递归SQL调用时,我已经相当迷茫了。这是可能的,还是应该在代码中这样做?

1 回复 | 直到 6 年前

sticky bit 6 年前

在MySQL8.0及更高版本中,可以使用递归CTE遍历层次结构。但是,由于您的客户已经拥有他们分配的公司的一些母公司(我觉得有点奇怪),这将导致重复。所以你需要得到不同的集合。为了方便起见,我使用了另一个CTE,但您也可以跳过它并执行 DISTINCT 直接在您的查询中。然后,您可以从CTE简单地查询客户的公司。

WITH RECURSIVE
cte1 AS
(
SELECT cu.customer_id,
       cu.company_id
       FROM customer cu
UNION ALL
SELECT ct.customer_id,
       co.parent_id company_id
       FROM cte1 ct
            INNER JOIN company co
                       ON ct.company_id = co.company_id
       WHERE co.parent_id IS NOT NULL
),
cte2 AS
(
SELECT DISTINCT
       ct.customer_id,
       ct.company_id
       FROM cte1 ct
)
SELECT company_id
       FROM cte2
       WHERE customer_id = 2;

DB Fiddle
当然,您也可以将客户ID上的条件转移到递归CTE的锚中。如果您只需要为一个客户提供这个服务,那么这可能会降低查询的成本。