mysql - 如何创建一个 SQL 调用，以递归方式将 DISTINCT id 和父 id 合并到一个列中？

Question

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

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

所以一个公司可以有一个父公司等等。

我还有一个查找表，用于将 客户 与其相关的 company 联系起来，如下所示:

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

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

例如，在上表中，如果我试图找到所有 company_id 的 where customer_id = 2，我希望得到:

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

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

不过，当涉及到递归 SQL 调用时，我很迷茫。这可能还是我应该在代码中执行此操作？

Answer 1

在 MySQL 8.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 的 anchor 中。如果您只需要为一个客户查询，这可能会降低查询成本。