选择按其值连接的键

这是一个典型的图形遍历问题-需要递归查询，从MariaDB 10.2.2开始可用。

这是一种方法，首先将边缘构建为连接节点的元组，然后迭代遍历数据集，并跟踪已访问的节点。通过跟踪最小访问节点，我们可以确定每个节点属于哪个组。

with recursive 
    edges as (
        select t1.lagr_number as lagr_number1, t2.lagr_number as lagr_number2
        from mytable t1
        inner join mytable t2 on t2.ltran_number = t1.ltran_number
    ),
    cte as (
        select lagr_number1, lagr_number2, concat(lagr_number1, ',', lagr_number2) as visited
        from edges
        union all
        select c.lagr_number1, e.lagr_number2, concat(c.visited, ',', e.lagr_number2)
        from cte c
        inner join edges e on e.lagr_number1 = c.lagr_number2
        where not find_in_set(e.lagr_number2, c.visited)
    )
select lagr_number1 as lagr_number, dense_rank() over(order by min(lagr_number2)) as grp
from cte
group by lagr_number1
order by grp, lagr_number1

DB Fiddle上的演示

样本数据：

lagr_number | ltran_number 
：---------- | ：----------- 
K000001 | V000001      
K000001 | V000004      
K000001 | V000005      
K000002 | V000001      
K000003 | V000002      
K000003 | V000003      
K000004 | V000005      
K000005 | V000007      
K000005 | V000008      
K000006 | V000009      
K000007 | V000009     

结果：

lagr_number | grp 
：---------- | -：
K000001 | 1 
K000002 | 1 
K000004 | 1 
K000003 | 2 
K000005 | 3 
K000006 | 4 
K000007 | 4