mysql - 获取 MySQL 中最长和最短的“链”？

Question

考虑这个模式：

create table Operation(id integer, name varchar(100));
create table Pipeline(operation_in integer, operation_out integer);

Pipeline有外键Operations，所以管道在某种程度上是链式的。operation_out可以为空。如何使用 MySQL 获取最长和最短管道链中的操作名称？

操作如下所示：

INSERT INTO Operation VALUES (1, 'operation one');

虽然管道看起来像这样：

INSERT INTO Pipeline VALUES (1, 2);
INSERT INTO Pipeline VALUES (2, 4);
INSERT INTO Pipeline VALUES (4, 7);
INSERT INTO Pipeline VALUES (7, NULL);

即这里的链将是 ID 为 1、2、4、7 的操作，预期结果如下：

"operation one", "operation two", "operation four"...

经过几个小时的研究，我不太确定我在寻找什么解决方案。

任何 MySQL 版本都适用。

标签： mysql

Answer 1

在 MySQL 8.x 中，您可以使用递归 CTE 来查找所需的链。

例如：

with recursive
a as (
  select
    p.operation_in,
    p.operation_out as current_out,
    o.name as op_names,
    concat('', p.operation_in) as chain,
    1 as size
  from pipeline p
  join operation o on o.id = p.operation_in
  where not exists (
    select 1 from pipeline p2 where p2.operation_out = p.operation_in
  )
  union all
  select
    a.operation_in,
    p.operation_out,
    concat(op_names, ', ', o.name),
    concat(chain, ',', p.operation_in),
    size + 1
  from a
  join pipeline p on p.operation_in = a.current_out
  join operation o on o.id = p.operation_in
),
chains as (
  select * from a where current_out is null
)
select op_names, chain, size
from chains
where size = (select max(size) from chains)  -- finds the longest one
   or size = (select min(size) from chains); -- finds the shortest one

结果：

op_names                           chain    size
---------------------------------  -------  ----
op-nine, op-six                    9,6         2
op-one, op-two, op-four, op-seven  1,2,4,7     4

我使用的数据脚本是：

create table operation (id integer, name varchar(100));
create table pipeline (operation_in integer, operation_out integer);

insert into operation values (1, 'op-one');
insert into operation values (2, 'op-two');
insert into operation values (4, 'op-four');
insert into operation values (6, 'op-six');
insert into operation values (7, 'op-seven');
insert into operation values (9, 'op-nine');

insert into pipeline values (1, 2);
insert into pipeline values (2, 4);
insert into pipeline values (4, 7);
insert into pipeline values (7, null);
insert into pipeline values (9, 6);
insert into pipeline values (6, null);