我正在嘗試創建一個遞回查詢，它將從 3 個不同的表中獲取屬性，但我無法正確連接這些表，因此我無法選擇所需的屬性：
我有3張桌子：
tb_register
CREATE TABLE olympic.tb_register (
athlete_id CHARACTER(7) NOT NULL,
round_number INT NOT NULL,
discipline_id INT NOT NULL,
register_position INT DEFAULT NULL,
CONSTRAINT pk_register PRIMARY KEY (athlete_id, round_number, discipline_id),
CONSTRAINT fk_register_athlete FOREIGN KEY (athlete_id) REFERENCES olympic.tb_athlete (athlete_id),
CONSTRAINT fk_register_round FOREIGN KEY (discipline_id, round_number) REFERENCES olympic.tb_round (discipline_id, round_number)
);
athlete_id round_number discipline_id register_position
"1349038" 3 16 0
"1393212" 3 16 1
"1318621" 3 16 2
"1451030" 3 16 3
"1343607" 3 16 4
"1435826" 3 16 5
INSERT INTO olympic.tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1349038', 3, 16, 0);
INSERT INTO olympic.tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1393212', 3, 16, 1);
INSERT INTO olympic.tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1318621', 3, 16, 2);
INSERT INTO olympic.tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1451030', 3, 16, 3);
INSERT INTO olympic.tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1343607', 3, 16, 4);
INSERT INTO olympic.tb_register(athlete_id, round_number, discipline_id, register_position) VALUES('1435826', 3, 16, 5);
tb_athlete
CREATE TABLE olympic.tb_athlete (
athlete_id CHARACTER(7) NOT NULL,
name CHARACTER VARYING(50) NOT NULL,
country CHARACTER VARYING(3) NOT NULL,
substitute_id CHARACTER(7) DEFAULT NULL,
CONSTRAINT pk_athlete PRIMARY KEY (athlete_id),
CONSTRAINT fk_athlete_substitute FOREIGN KEY (substitute_id) REFERENCES olympic.tb_athlete (athlete_id)
);
athlete_id athlete_name country
"1349038" "AALERUD Katrine" "NOR"
"1393212" "ABASS Abobakr" "SUD"
"1451030" "ABDALLA Abubaker Haydar" "QAT"
"1444255" "ABDEL LATIF Radwa" "EGY"
INSERT INTO olympic.tb_athlete(athlete_id, name, country, substitute_id) VALUES('1346266','AALERUD Katrine','NOR',NULL);
INSERT INTO olympic.tb_athlete(athlete_id, name, country, substitute_id) VALUES('1344792','ABASS Abobakr','SUD',NULL);
INSERT INTO olympic.tb_athlete(athlete_id, name, country, substitute_id) VALUES('1328854','ABDALLA Abubaker Haydar','QAT',NULL);
INSERT INTO olympic.tb_athlete(athlete_id, name, country, substitute_id) VALUES('1306332','ABDEL LATIF Radwa','EGY',NULL);
tb_discipline
CREATE TABLE olympic.tb_discipline (
discipline_id INT NOT NULL,
name CHARACTER VARYING(50) NOT NULL,
inventor CHARACTER VARYING(50) NOT NULL,
type CHARACTER VARYING(10) NOT NULL,
object_type CHARACTER VARYING(20) DEFAULT NULL,
CONSTRAINT ck_discipline_type CHECK (type IN ('RUN', 'JUMP', 'THROW')),
CONSTRAINT pk_discipline PRIMARY KEY (discipline_id)
);
discipline_id discipline_name inventor type
16 "Triathlon" "Carina Melina" "RUN"
INSERT INTO olympic.tb_discipline(discipline_id, name, inventor, type, object_type) VALUES(16,'Triathlon','Carina Melina','RUN',null);
期望輸出
我正在嘗試創建一個遞回查詢來獲得這個結果：
discipline_name, round_number, register_position, a_position
Triathlon, 3, 0, 0:AALERUD Katrine,
Triathlon, 3, 1, 0:AALERUD Katrine -> 1: ABASS Abobakr
Triathlon, 3, 2, 0:AALERUD Katrine -> 1: ABASS Abobakr -> ABDALLA Abubaker Haydar
其中 a_position 是運動員姓名及其各自位置的串列。
我的代碼：
WITH RECURSIVE athlete_hierarchy AS (
SELECT
discipline_name,
round_number,
register_position,
CAST (athlete_name AS TEXT) AS a_position,
register_position AS first_athlete
FROM
olympic.tb_register
JOIN tb_discipline
ON discipline_id = tb_discipline.discipline_id
JOIN tb_athlete
ON athlete_id = tb_athlete.athlete_id;
WHERE
round_number IS 3
UNION ALL
SELECT
a.discipline_name,
a.round_number,
a.register_position,
CAST ( a.athlethe_name || ' -> ' || a2.a_position AS TEXT) AS a_position,
c2.first_athlete AS first_athlete
FROM
olympic.tb_register INNER JOIN athlete_hierarchy
ON (a.athlete_id = a2.athlete_id)
)
SELECT
discipline_name,
round_number,
register_position,
a_position,
first_athlete
FROM athlete_hierarchy;
任何人都可以幫助我了解在創建 CTE 遞回查詢時如何加入 3 個表嗎？
uj5u.com熱心網友回復：
這是解決方案：
WITH RECURSIVE athlete_hierarchy (discipline_id, discipline_name, round_number, register_position, a_position) AS
(SELECT
d.discipline_id,
d.name,
r.round_number,
r.register_position,
r.register_position || ':' || CAST (a.name AS TEXT)
FROM tb_register AS r
JOIN tb_discipline AS d
ON r.discipline_id = d.discipline_id
JOIN tb_athlete AS a
ON r.athlete_id = a.athlete_id
WHERE r.round_number = 3
AND r.register_position = 0
UNION ALL
SELECT
ah.discipline_id,
ah.discipline_name,
ah.round_number,
r.register_position,
ah.a_position || ' -> ' || r.register_position || ':' || CAST (a.name AS TEXT)
FROM tb_register AS r
INNER JOIN athlete_hierarchy AS ah
ON ah.discipline_id = r.discipline_id
AND ah.round_number = r.round_number
AND ah.register_position = r.register_position - 1
INNER JOIN tb_athlete AS a
ON r.athlete_id = a.athlete_id
)
SELECT discipline_name, round_number, register_position, a_position
FROM athlete_hierarchy
ORDER BY register_position
資料樣本的測驗結果在db<>fiddle 中。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/403620.html
標籤：
上一篇：為什么我們不應該顯式地將undefined分配給javascript中的變數？
下一篇：還從SQL查詢回傳空匹配項