棘手的CTE-递归SQL(编辑我的查询) [英] Tricky CTE - recursive sql (editing my query)
问题描述
我想编辑查询，以使查询更棘手.
I want to edit my query in order to get something a little more ticky.
目标是获取每个客户的批准工作流，并以这种方式显示该信息:
The goal is to obtain the approval workflow for every customer, displaying that information in this way:
客户| APPROVER1 | APPROVER2 | APPROVER3 |批准人4
CLIENT | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4
以前，我有一个称为实体的表
Previously, i had 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);
注意:
12被分配给数学安德鲁... 308是表示 马特·安德鲁(Matt Andrew)是客户
12 was assigned to Math Andrew... 308 is the number that says that Matt Andrew is a CLIENT
13被分配给John Connor ... 308是表明John Connor是客户的数字
13 was assigned to John Connor... 308 is the number that says that John Connor is a CLIENT
由于数学安德鲁(Andrew)和约翰·康纳(John Connor)是客户(也称为客户)，因此必须将他们链接到一个或多个批准者
Because Math Andrew and John Connor are CLIENTS (also known as CUSTOMERS) they must be linked to one or more APPROVERS
一个客户可能有1个批准者，2个批准者，3个批准者或4个批准者，实体表中存在不同的批准者.
A client could have 1 APPROVER, OR 2 APPROVERS OR 3 APPROVERS OR 4 APPROVERS, there exist different approvers inside entities table.
当我说客户可以拥有" 1个或多个批准时，我的意思是:
When i say that a client "could have" 1 or more APPROVERS i mean this:
客户端-批准人4(这是1-1的关系)PS:客户端将 总是以某种方式与批准人有关
CLIENT - APPROVER4 (this is a 1-1 relationship) PS: A CLIENT WILL ALWAYS BE RELATED TO the APPROVER4 IN SOME WAY OR ANOTHER
客户-审批人1-审批人4(在这种情况下，将为2 关系.一个:CLIENT-APPROVER1和另一个APPROVER1-APPROVER4)
CLIENT - APPROVER1 - APPROVER4 (in this case there Will be 2 relations.. ONE: CLIENT-APPROVER1 and another APPROVER1-APPROVER4)
客户-审批人1-审批人2-审批人4(在这种情况下， 3关系.一个:CLIENT-APPROVER1，APPROVER1- APPROVER2和 APPROVER2-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)
表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,'CLIENT-APPROVER1')
客户端-APPROVER2:
(471,'CLIENT-APPROVER2')
客户端-批准人3:
(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');
这很重要:当客户链接到一个批准人时， RELATION是在关系表中创建的.
THIS IS IMPORTANT: when a client is linked to one approver, a NEW RELATION is created inside relationships table.
表关系:
(787,459,12,18),
(788,460,18,20),
(789,463,20,21),
(790,467,21,26);
787是行创建时分配的数字
787 IS THE NUMBER THAT WAS ASSIGNED WHEN THAT ROW WAS CREATED
459表示关系:客户-批准人
459 REPRESENTS THE RELATION: CLIENT - APPROVER
12 CHAIN1-MathAndre是客户端
12 CHAIN1-MathAndre is the client
18是批准人
遵循以下想法:
APPROVER1已链接到APPROVER2:
(788,460,18,20)
APPROVER2已链接到APPROVER3:
(789,463,20,21)
APPROVER3已链接到APPROVER4:
(790,467,21,26)
所以，我在屏幕上显示它:
So, i display this in screen:
|CLIENT | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4|
|CHAIN1-MathAndrew | ZATCH | Ger | Mar | John |
|CHAIN2-JohnConnor | MAX | | Mario | Steven|
|CHAIN3-MarioShapiro | IVAN | | | John |
这是我的小提琴:
这是我的查询:
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
现在，这就是我想要做的:
假设我有一个名为new_table的新表，并且我稍微修改了表实体:
SUPPOSE that i have a new table called new_table and i modified table entities a little bit:
(12, 'Math Andrew', 308, 45)
(13, 'John Connor', 308, 46)
(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);
表new_table
(45,'Math Andrew', 'Chain1')
(45,'Math Andrew', 'Chain2')
(46, 'John Connor', 'Chain1')
(46, ''John Connor', 'Chain2')
和表关系如下所示:
(787,459,'45-Chain1',18)
(788,460,18,20)
(789,463,20,21)
(790,467,21,26)
因此，我想将表实体与表 new_table 连接起来，获得关系45-Chain1，然后，在表关系中找到45-Chain1，并得到类似的结果(并对所有不同的客户端执行相同的操作):
So, i want to join table entities with table new_table, get the relation 45-Chain1 and then, found 45-Chain1 within table relationships and get something like this (and do the same for all the different clients):
|CLIENT | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4|
|45-Chain1 | ZATCH | Ger | Mar | John |
我一直在尝试解决这个问题，但没有成功.
I have been trying without success to solve this.
能请你帮我吗?
推荐答案
我尝试过.
并进行了标准化，增加了一个chains表.
I tried.
And normalized a bit extra, adding a chains table.
这是我的尝试:
create table entity_types
(
entity_type_id int primary key,
entity_type_name varchar(32) not null
);
✓
create table relation_types
(
relation_type_id int primary key,
relation_type_name varchar(32) not null
);
✓
create table chains
(
chain_id int primary key,
chain_name varchar(30) not null
);
✓
create table entities
(
entity_id int primary key,
entity_name varchar(32) not null,
entity_type_id int not null,
chain_id int,
foreign key (entity_type_id)
references entity_types(entity_type_id),
foreign key (chain_id)
references chains(chain_id)
);
✓
create table relationships
(
relationship_id int primary key,
relation_type_id int not null,
entity_id_1 int not null,
entity_id_2 int not null,
foreign key (relation_type_id)
references relation_types(relation_type_id)
);
✓
create table entity_chains
(
entity_id int not null,
chain_id int not null,
primary key (entity_id, chain_id),
foreign key (chain_id)
references chains(chain_id),
foreign key (entity_id)
references entities(entity_id)
);
✓
INSERT INTO entity_types
(entity_type_id, entity_type_name) VALUES
(308,'CLIENT'),
(309,'APPROVER1'),
(310,'APPROVER2'),
(311,'APPROVER3'),
(312,'J3 APPROVER4'),
(313,'J4 APPROVER4'),
(314,'J5 APPROVER4'),
(315,'J6 APPROVER4'),
(316,'J7 APPROVER4');
✓
INSERT INTO relation_types
(relation_type_id, relation_type_name) VALUES
(444,'J6 CLIENT-APPROVER4'),
(445,'J3 CLIENT-APPROVER4'),
(446,'J4 CLIENT-APPROVER4'),
(447,'J10 CLIENT-APPROVER4'),
(448,'J4 CLIENT-APPROVER4'),
(449,'J5 CLIENT-APPROVER4'),
(450,'J10 CLIENT-APPROVER4'),
(451,'J3 CLIENT-APPROVER4'),
(452,'J8 CLIENT-APPROVER4'),
(453,'J5 CLIENT-APPROVER4'),
(454,'J6 CLIENT-APPROVER4'),
(455,'J7 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');
✓
insert into chains
(chain_id, chain_name) values
(45,'Chain1'),
(46,'Chain2');
✓
INSERT INTO entities
(entity_id, entity_name,
entity_type_id, chain_id) VALUES
(12, 'Math Andrew', 308, 45),
(13, 'John Connor', 308, 46),
(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);
✓
INSERT INTO relationships
(relationship_id, relation_type_id,
entity_id_1, entity_id_2) VALUES
(787,459,12,18),
(788,460,18,20),
(789,463,20,21),
(790,467,21,26);
✓
insert into entity_chains
(entity_id, chain_id) values
(12, 45),
(12, 46),
(13, 45),
(13, 46);
✓
WITH RECURSIVE RCTE AS
(
SELECT
ent.chain_id,
entch.entity_id as entity_id_0,
0 as lvl,
0 as entity_id_1,
entch.entity_id as entity_id_2,
0 as relation_type_id
FROM entities ent
JOIN entity_chains entch
ON entch.chain_id = ent.chain_id
UNION ALL
SELECT
cte.chain_id,
cte.entity_id_0,
lvl+1,
rel.entity_id_1,
rel.entity_id_2,
rel.relation_type_id
FROM RCTE cte
JOIN relationships rel
ON rel.entity_id_1 = cte.entity_id_2
),
CTE AS
(
SELECT
rcte.*,
chains.chain_name,
ent0.entity_name as entity_name_0,
-- reltype.relation_type_name,
-- enttype2.entity_type_name as entity_type_name_2,
-- ent1.entity_name as entity_name_1,
ent2.entity_name as entity_name_2
FROM RCTE rcte
JOIN chains ON chains.chain_id = rcte.chain_id
JOIN entities ent0 ON ent0.entity_id = rcte.entity_id_0
JOIN entities ent2 ON ent2.entity_id = rcte.entity_id_2
-- LEFT JOIN entity_types enttype2 ON enttype2.entity_type_id = ent2.entity_type_id
-- LEFT JOIN relation_types reltype ON reltype.relation_type_id = rcte.relation_type_id
-- LEFT JOIN entities ent1 ON ent1.entity_id = rcte.entity_id_1
)
/*
SELECT * FROM CTE WHERE lvl > 0
ORDER BY chain_name, entity_id_0, lvl;
*/
SELECT
REPLACE(CONCAT(entity_name_0,'-',chain_name),' ','') as chain_client,
max(case when lvl=1 then entity_name_2 end) as approver1,
max(case when lvl=2 then entity_name_2 end) as approver2,
max(case when lvl=3 then entity_name_2 end) as approver3,
max(case when lvl=4 then entity_name_2 end) as approver4
FROM CTE cte
WHERE lvl > 0
GROUP BY chain_name, entity_name_0
ORDER BY chain_client;
chain_client | approver1 | approver2 | approver3 | approver4
:---------------- | :-------- | :-------- | :-------- | :--------
MathAndrew-Chain1 | ZATCH | Ger | Mar | John
MathAndrew-Chain2 | ZATCH | Ger | Mar | John
db<>小提琴此处
这篇关于棘手的CTE-递归SQL(编辑我的查询)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
