尝试获取准确的信息(CTE-递归) [英] Trying to obtain the accurate information (CTE - recursive)
问题描述
我有不同的表，目标是为每个客户获取批准工作流程，并以此方式显示该信息:
>客户| APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4
首先，我有一个称为实体的表
(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是表示 马特·安德鲁(Matt Andrew)是客户
13被分配给John Connor ... 308是表示 约翰·康纳(John Connor)是客户
由于Math Andrew和John Connor是客户(也称为客户)，因此必须链接到一个或多个批准者
一个客户可能有1个批准者，2个批准者，3个批准者或4个批准者，实体表中存在不同的批准者.
当我说一个客户可能"拥有1个或多个批准者时，我的意思是
客户端-批准人4(这是1-1的关系)PS:客户端将 总是以某种方式与批准人有关
客户-审批人1-审批人4(在这种情况下，将为2 关系.一个:CLIENT-APPROVER1和另一个APPROVER1-APPROVER4)
客户-审批人1-审批人2-审批人4(在这种情况下， 3关系.一个:CLIENT-APPROVER1，APPROVER1- APPROVER2和 APPROVER2-APPROVER4)
等等...(希望您能想到这个主意)
表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')
APPROVER 1 -APPROVER 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')
这很重要:当客户链接到一个批准人时， RELATION是在关系表中创建的.
表关系:
(787,459,12,18)
(788,460,18,20)
(789,463,20,21)
(790,467,21,26)
787是排成一行时分配的数字
459关系:客户-批准人
CHAIN1-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 |
最后2行只是一个示例
这是我到目前为止(正在运行)的内容:
但是它显示信息时未显示列名(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
您应该使用条件聚合来格式化所需的数据.尝试以下解决方案，假设您拥有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
该解决方案使用您的SQL查询并添加必要的信息以进行表格式化:(1)row_id和(2)col_id.然后将这些值用于条件放大以创建表.
I have different tables and the goal is to obtain the approval workflow for every customer, displaying that information in this way:
> CLIENT | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4
First of all, i have a table called entities
(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);
DO NOTE:
12 was assigned to Math Andrew... 308 is the number that says that Matt Andrew is a CLIENT
13 was assigned to John Connor... 308 is the number that says that John Connor is a CLIENT
Because Math Andrew and John Connor are CLIENTS (also known as CUSTOMERS) they must be linked to one or more APPROVERS
A client could have 1 APPROVER, OR 2 APPROVERS OR 3 APPROVERS OR 4 APPROVERS, there exist different approvers inside entities table.
When i say that a client "could have" 1 or more APPROVERS i mean this
CLIENT - APPROVER4 (this is a 1-1 relationship) PS: A CLIENT WILL ALWAYS BE RELATED TO the APPROVER4 IN SOME WAY OR ANOTHER
CLIENT - APPROVER1 - APPROVER4 (in this case there Will be 2 relations.. ONE: CLIENT-APPROVER1 and another APPROVER1-APPROVER4)
CLIENT - APPROVER1 - APPROVER2 - APPROVER4 (in this case there Will be 3 relations.. ONE: CLIENT-APPROVER1, APPROVER1- APPROVER2 AND APPROVER2 - APPROVER4)
AND SO ON... (hopefully you get the idea)
table 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');
table 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');
Types of relations:
CLIENT - APPROVER1 : (459,'CLIENT-APPROVER1')
CLIENT - APPROVER2 : (471,'CLIENT-APPROVER2')
CLIENT - APPROVER3 : (461,'APPROVER1-APPROVER3')
CLIENT - APPROVER4:
(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')
APPROVER 1 -APPROVER 2:
(460,'APPROVER1-APPROVER2')
APPROVER 2 - APPROVER 3:
(463,'APPROVER2-APPROVER3')
APPROVER 3 - APPROVER 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')
THIS IS IMPORTANT: when a client is linked to one approver, a NEW RELATION is created inside relationships table.
Table relationships:
(787,459,12,18)
(788,460,18,20)
(789,463,20,21)
(790,467,21,26)
787 IS THE NUMBER THAT WAS ASSIGNED WHEN THAT ROW WAS CREATED
459 REPRESENTS THE RELATION: CLIENT - APPROVER
CHAIN1-MathAndre is theclient
18 is the approver
Following the idea:
APPROVER1 was linked to APPROVER2
(788,460,18,20)
APPROVER2 was linked to APPROVER3
(789,463,20,21)
APPROVER3 was linked to APPROVER4
(790,467,21,26)
So, i would like to display this in screen:
|CLIENT | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4|
|CHAIN1-MathAndrew | ZATCH | Ger | Mar | John |
|CHAIN2-JohnConnor | MAX | | Mario | Steven|
|CHAIN3-MarioShapiro | IVAN | | | John |
The last 2 rows are just an example
This is what i have so far (it IS working):
but it is displaying the information without showing columns name (CLIENT, APPROVER1, APPROVER2, APPROVER3, APPROVER4).. This is displaying this:
CHAIN1-MathAndrew-ZATCH-Ger-Mar-John
I want to display the data in this way:
|CLIENT | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4|
|CHAIN1-MathAndrew | ZATCH | Ger | Mar | John |
|CHAIN2-JohnConnor | MAX | | Mario | Steven|
|CHAIN3-MarioShapiro | IVAN | | | John |
I am pretty lost, could you please help me?
EDIT:
The maximum amount of approvers is: 4
You should use conditional aggregation to format your data as you want. Try the following solution, where I assume that you have MySQL ver.8 and the window functions are available:
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
The solution uses your SQL query and adds the necessary information for table formating: (1) row_id, and (2) col_id. These values are then used in conditional aggeration to create the table.
这篇关于尝试获取准确的信息(CTE-递归)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
