sql - 同一张表的SQL递归查询

Question

我是 SQL 新手，我正在尝试对同一个表进行递归查询以查找“主代理”的代理我有一个看起来像这样的表（它可以增长到任意数量的行和深度）

所以我需要这样的结果：

master_id => broker_id

我已经检查了如何做到这一点，我得到了：

WITH admin_has_master_brokers
AS (
    SELECT DISTINCT master_broker_id, admin_id
        FROM admin_has_master_brokers
            
    UNION ALL
    /*I DO NOT KNOW HOW TO DO THIS SECTION*/
    SELECT    
        master_broker_id, admin_id
    FROM admin_has_master_brokers
)
SELECT 
    *
FROM 
    admin_has_master_brokers
ORDER BY master_broker_id ASC

但我不明白如何做递归部分只得到我需要的结果，因为我得到了这个：

任何的想法？

标签： sqlmariadb

Answer 1

Povided original table是Mytable查询列出每个master_broker_id的所有后代。

WITH RECURSIVE admin_has_master_brokers
AS (
    SELECT DISTINCT master_broker_id master, master_broker_id, admin_id
    FROM mytable
       
    UNION ALL

    SELECT a.master,  
        m.master_broker_id, m.admin_id
    FROM admin_has_master_brokers a
    JOIN mytable m ON m.master_broker_id = a.admin_id
)
SELECT DISTINCT master, admin_id    
FROM 
    admin_has_master_brokers
ORDER BY master, admin_id