Postgres内部联接查询三个表

Question

我有一个名为run:的表

结果通过通过通过通过失败失败失败失败

场景		日期	系统
场景1	07-01	A
场景1	07-01	B
场景1	07-01	C
场景1	07-01	D
场景1	07-02	A
场景1	07-02	B
场景1	07-02	C
场景1	07-02	D

我真的不知道问题出在哪里。

run似乎是某个场景、某个日期和某个系统的运行，结果是"；通过"；或"；失败"；。

第一个交集表，我称之为pj2scen，将场景映射到它所属的项目

第二个似乎是一个查找表，为我们提供了某个系统在某个项目中获得的优先级。

因此，从pj2scen表中获得项目，通过使用run表中的场景进行连接，然后使用获得的项目和run表中的系统与prio表进行连接，这是显而易见的。我问自己，还有什么？

顺便说一句，我总是用保留字重命名列/表作为名称，比如date、system或priority

WITH
-- your input - don't use in the final query
run(Scenario,Dt,Sys,Result) AS (
SELECT 'Scen1',DATE '2022-07-01','A','PASS'
UNION ALL SELECT 'Scen1',DATE '2022-07-01','B','PASS'
UNION ALL SELECT 'Scen1',DATE '2022-07-01','C','PASS'
UNION ALL SELECT 'Scen1',DATE '2022-07-01','D','PASS'
UNION ALL SELECT 'Scen1',DATE '2022-07-02','A','FAIL'
UNION ALL SELECT 'Scen1',DATE '2022-07-02','B','FAIL'
UNION ALL SELECT 'Scen1',DATE '2022-07-02','C','FAIL'
UNION ALL SELECT 'Scen1',DATE '2022-07-02','D','FAIL'
)
,
pj2scen (Project,Scenario) AS (
SELECT 'Proj1','Scen1'
)
,
prio(Project,Sys,Prio) AS (
SELECT 'Proj1','A',2
UNION ALL SELECT 'Proj1','B',3
UNION ALL SELECT 'Proj1','C',1
UNION ALL SELECT 'Proj1','D',4
)
-- end of your input, real query starts here ...
SELECT
run.*
, prio.prio
FROM run 
JOIN pj2scen USING(scenario)
JOIN prio USING(project,sys)
ORDER BY result DESC, sys; 
-- out  Scenario |     Dt     | Sys | Result | prio 
-- out ----------+------------+-----+--------+------
-- out  Scen1    | 2022-07-01 | A   | PASS   |    2
-- out  Scen1    | 2022-07-01 | B   | PASS   |    3
-- out  Scen1    | 2022-07-01 | C   | PASS   |    1
-- out  Scen1    | 2022-07-01 | D   | PASS   |    4
-- out  Scen1    | 2022-07-02 | A   | FAIL   |    2
-- out  Scen1    | 2022-07-02 | B   | FAIL   |    3
-- out  Scen1    | 2022-07-02 | C   | FAIL   |    1
-- out  Scen1    | 2022-07-02 | D   | FAIL   |    4

查询应该是这样的：

with main as (
select 'Scen1' scenario, '07-01' datex, 'A' system,  'PASS' result union
select 'Scen1' scenario, '07-01' datex, 'B' system,  'PASS' result union
select 'Scen1' scenario, '07-01' datex, 'C' system,  'PASS' result union
select 'Scen1' scenario, '07-01' datex, 'D' system,  'PASS' result union
select 'Scen1' scenario, '07-02' datex, 'A' system,  'FAIL' result union
select 'Scen1' scenario, '07-02' datex, 'B' system,  'FAIL' result union
select 'Scen1' scenario, '07-02' datex, 'C' system,  'FAIL' result union
select 'Scen1' scenario, '07-02' datex, 'D' system,  'FAIL' result 
), project as(
select 'Proj1' project, 'Scen1' scenario
), priority as (
select 'Proj1' project, 'A' system, '2' score union
select 'Proj1' project, 'B' system, '3' score union
select 'Proj1' project, 'C' system, '1' score union
select 'Proj1' project, 'D' system, '4' score 
)select m.scenario, m.datex, m.system, m.result, o.score from main m
join project p on m.scenario = p.scenario
join priority o on o.project = p.project
and o.system = m.system
order by 1,2,3
;

Fiddle