尝试获取准确的信息（CTE-递归）

Question

我有不同的表， 目标 是获得每个客户的批准工作流，并以这种方式显示该信息：

> 客户| APPROVER1 | APPROVER2 | APPROVER3 | 审批人4

首先，我有一个称为实体的表

(12, 'Math Andrew', 308, 'CHAIN1-MathAndrew')
(13, 'John Connor', 308, 'CHAIN2-JohnConnor')
(18, 'ZATCH', 309, null),
(19, 'MAX', 309, null),
(20, 'Ger',310, null),
(21, 'Mar',310, null),
(22, 'Maxwell',311, null),
(23, 'Ryan',312, null),
(24, 'Juy',313, null),
(25, 'Angel',314, null),
(26, 'John',315, null);

注意：

12被分配给数学安德鲁… 308是表示马特·安德鲁是客户的数字

13被分配给John Connor … 308是表示John Connor是CLIENT的数字

由于Math Andrew和John Connor是CLIENTS（也称为客户），因此必须将其链接到一个或多个APPROVERS

一个客户可能有1个批准人，2个批准人，3个批准人或4个批准人，实体表中存在不同的批准人。

当我说客户“可能”拥有1个或多个批准时，我的意思是

客户-审批人4（这是1-1的关系）PS：总是会以某种方式将客户与审批人4相关

CLIENT-APPROVER1-APPROVER4（在这种情况下，将有2种关系。一个：CLIENT-
APPROVER1和另一个APPROVER1-APPROVER4）

客户-审批人1-审批人2-审批人4（在这种情况下，将有3个关系。一个：客户审批人1，审批人1-审批人2和审批人2-审批人4）

等等…（希望您能想到这个主意）

表type_entities

(308,'CLIENT'),
(309,'APPROVER1'),
(310,'APPROVER2'),
(311,'APPROVER3'),
(312,'J3 APPROVER4'),
(313,'J4 APPROVER4'),
(314,'J5 APPROVER4'),
(315, 'J6 APPROVER4'),
(316,'J7 APPROVER4');

表type_relation

(444,'J6 CLIENT-APPROVER4'),
(445,'J3 CLIENT-APPROVER4'),
(446,'J4 CLIENT-APPROVER4'),
(447,'J10 CLIENT-APPROVER4'),
(449,'J5 CLIENT-APPROVER4'),
(453,'J5 CLIENT-APPROVER4'),
(456,'J7 CLIENT-APPROVER4'),
(457,'J8 CLIENT-APPROVER4'),
(458,'CLIENT-APPROVER3'),
(459,'CLIENT-APPROVER1'),
(460,'APPROVER1-APPROVER2'),
(461,'APPROVER1-APPROVER3'),
(462,'J3 APPROVER1-APPROVER4'),
(463,'APPROVER2-APPROVER3'),
(464,'J3 APPROVER3-APPROVER4'),
(465,'J4 APPROVER3-APPROVER4'),
(466,'J5 APPROVER3-APPROVER4'),
(467,'J6 APPROVER3-APPROVER4'),
(468,'J7 APPROVER3-APPROVER4'),
(469,'J8 APPROVER3-APPROVER4'),
(470,'J10 APPROVER3-APPROVER4'),
(471,'CLIENT-APPROVER2');

关系类型：

客户-审批人1：（459，“客户审批人1”）

客户-审批人2：（471，“客户审批2”）

客户-APPROVER3：（461，“ APPROVER1-APPROVER3”）

客户-审批者4：

（445，’J3 CLIENT-APPROVER4’）

（446，’J4 CLIENT-APPROVER4’）

（449，’J5 CLIENT-APPROVER4’）

（444，’J6 CLIENT-APPROVER4’）

（456，’J7 CLIENT-APPROVER4’）

（457，’J8 CLIENT-APPROVER4’）

（447，’J10 CLIENT-APPROVER4’）

批准人1-批准人2：

（460，’APPROVER1-APPROVER2’）

批准人2-批准人3：

（463，’APPROVER2-APPROVER3’）

批准人3-批准人4：

（464，’J3 APPROVER3-APPROVER4’）

（465，’J4 APPROVER3-APPROVER4’）

（466，’J5 APPROVER3-APPROVER4’）

（467，’J6 APPROVER3-APPROVER4’）

（468，’J7 APPROVER3-APPROVER4’）

（469，’J8 APPROVER3-APPROVER4’）

（470，’J10 APPROVER3-APPROVER4’）

这很重要：当客户链接到一个批准人时，将在 关系表中 创建一个新关系。

表关系：

(787,459,12,18)
(788,460,18,20)
(789,463,20,21)
(790,467,21,26)

787是行创建时分配的编号
459表示该关系：客户- 批准者
链1-MathAndre是客户
18是批准者

遵循的想法：

APPROVER1已链接到APPROVER2

（788,460,18,20）

APPROVER2已链接到APPROVER3

（789,463,20,21）

APPROVER3已链接到APPROVER4

（790,467,21,26）-

所以，我想在屏幕上显示此：

|CLIENT               | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4|
|CHAIN1-MathAndrew    |   ZATCH   |   Ger     |    Mar    |    John  |
|CHAIN2-JohnConnor    |    MAX    |           |    Mario  |    Steven|
|CHAIN3-MarioShapiro  |    IVAN   |           |           |    John  |

最后两行只是一个例子

这是我到目前为止（正在运行）的内容：

LINK_sample_SQL

但是它在显示信息时未显示列名（CLIENT，APPROVER1，APPROVER2，APPROVER3，APPROVER4）。

CHAIN1-MathAndrew-ZATCH-Ger-Mar-John

我想以这种方式显示数据：

|CLIENT               | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4|
|CHAIN1-MathAndrew    |   ZATCH   |   Ger     |    Mar    |    John  |
|CHAIN2-JohnConnor    |    MAX    |           |    Mario  |    Steven|
|CHAIN3-MarioShapiro  |    IVAN   |           |           |    John  |

我很迷路，请你帮我一下？

编辑：

批准者的最大数量为：4

Answer 1

您应该使用条件聚合来格式化所需的数据。尝试以下解决方案，假设您拥有MySQL ver.8，并且窗口功能可用：

WITH recursive relationships_CTE as (
  select e.id, e.description AS name, 1 col_id, 
    row_number() over (order by e.id) row_id
  from entities e
  where e.description like 'CHAIN%'
    UNION ALL
  select r.description_entitiy_2, e.name, col_id+ 1, row_id
  from relationships_CTE cte
  left join relationships r
    on r.description_entitiy_1 = cte.id
  join entities e 
    on r.description_entitiy_2 = e.id
)
select 
  max(case when col_id = 1 then name end) client,
  max(case when col_id = 2 then name end) approver1,
  max(case when col_id = 3 then name end) approver2,
  max(case when col_id = 4 then name end) approver3,
  max(case when col_id = 5 then name end) approver4
from relationships_CTE
group by row_id

DB-FIDDLE DEMO

该解决方案使用您的SQL查询并添加必要的信息以进行表格式化：（1）row_id和（2）col_id。然后将这些值用于条件放大以创建表。