sql - 如何在单个查询中执行具有不同参数的多个连接

Question

我有两个表，question(question_id) 和 question_exclusion(question_type, question_sub_type, question_id)

如果我指定 question_type 和 question_sub_type，我就能做到。

SELECT *
FROM question AS t1
         LEFT JOIN (SELECT t.question_id
                    FROM question_exclusion as t
                    WHERE t.question_type = 'A'
                      AND t.question_sub_type = 'A_1') AS t2
                   ON t1.question_id = t2.question_id
WHERE t2.question_id is null;

但我想要实现的是在一个查询中获取所有可能的 question_type 和 questions_sub_type

的所有问题

question_type 和 questions_sub_type 是动态参数 在查询执行之前我不知道确切的值

更新 1:

实际数据:

表:问题

question_id|
42
10
2
36
49

表:question_exclusion

question_type|question_sub_type|question_id|
A            | A_1             | 42
A            | A_1             | 10
A            | A_2             | 10
B            | B_1             | 36 
C            | null            | 2

预期结果:

question_type|question_sub_type|question_id
A            | A_1             | 2
A            | A_1             | 36
A            | A_1             | 49
A            | A_2             | 42
A            | A_2             | 2
A            | A_2             | 36
A            | A_2             | 49
B            | B_1             | 42
B            | B_1             | 10
B            | B_1             | 2
B            | B_1             | 49
C            | null            | 42
C            | null            | 10
C            | null            | 36
C            | null            | 49

它就像每个类型和子类型组合的列表列表考虑排除表

例如:

type=A, sub_type=A_1 -> (select * from questions) - (select * from question_exclusion where type='A' and sub_type='A_1')
+
type=A, sub_type=A_2 -> (select * from questions) - (select * from question_exclusion where type='A' and sub_type='A_2')
+
type=B, sub_type=B_1 -> (select * from questions) - (select * from question_exclusion where type='B' and sub_type='B_2')

当然我可以查询所有不同的(类型，sub_type)并通过结合联合进行另一个查询

SELECT *
FROM question AS t1
         LEFT JOIN (SELECT t.question_id
                    FROM question_exclusion as t
                    WHERE t.question_type = 'A'
                      AND t.question_sub_type = 'A_1') AS t2
                   ON t1.question_id = t2.question_id
WHERE t2.question_id is null
UNION
SELECT *
FROM question AS t1
         LEFT JOIN (SELECT t.question_id
                    FROM question_exclusion as t
                    WHERE t.question_type = 'B'
                      AND t.question_sub_type = 'B_1') AS t2
                   ON t1.question_id = t2.question_id
WHERE t2.question_id is null
...
...
N times for all type and sub_type

我正在寻找另一种在单个查询中执行此操作的可靠方法

Answer 1

\i tmp.sql

create table question
        (question_id integer not null primary key)
        ;
INSERT INTO question(question_id) VALUES
( 42) , ( 10) , ( 2) , ( 36) , ( 49) ;

create table question_exclusion
        ( question_type text
        , question_sub_type text
        , question_id integer REFERENCES question( question_id)
        );

INSERT INTO question_exclusion(question_type, question_sub_type, question_id) VALUES
 ('A' , 'A_1' , 42 ) , ('A' , 'A_2' , 10 ) , ('B' , 'B_1' , 36  ) , ('C' , null  , 2 ) ;

WITH types AS (
        select distinct question_type, question_sub_type
        FROM question_exclusion
        )
SELECT t.question_type, t.question_sub_type, q.question_id
FROM question q
JOIN types t ON NOT EXISTS (
        SELECT * FROM question_exclusion x
        WHERE 1=1
        AND x.question_id = q.question_id
        AND x.question_type = t.question_type
        AND x.question_sub_type = t.question_sub_type
        )
ORDER BY t.question_type, t.question_sub_type
        ;

---

修改:

---

WITH types AS (
        select distinct question_type, question_sub_type
        FROM question_exclusion
        )
SELECT t.question_type, t.question_sub_type, q.question_id
FROM question q
CROSS JOIN types t
WHERE NOT EXISTS (
        SELECT * FROM question_exclusion x
        WHERE 1=1
        AND x.question_id = q.question_id
        AND x.question_type = t.question_type
        AND x.question_sub_type = t.question_sub_type
        )
ORDER BY t.question_type, t.question_sub_type
        ;

---

没有区别于

---

WITH types AS (
        select distinct question_type, question_sub_type
        FROM question_exclusion
        )
SELECT t.question_type, t.question_sub_type, q.question_id
FROM question q
CROSS JOIN types t
WHERE NOT EXISTS (
        SELECT * FROM question_exclusion x
        WHERE 1=1
        AND x.question_id = q.question_id
        AND (x.question_type, x.question_sub_type) IS NOT DISTINCT FROM
            (t.question_type, t.question_sub_type)
        )
ORDER BY t.question_type, t.question_sub_type
        ;