sql - 如何在单个查询中执行具有不同参数的多个连接
问题描述
我有两个表,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;
但是我想要实现的是在单个查询中获取所有带有 questions_ids 的问题,以获取所有可能的question_type
和questions_sub_type
question_type
并且questions_sub_type
是动态参数在查询执行之前我不知道确切的值
更新1:
实际数据:
桌子:question
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
它就像每个类型和 sub_type 组合的列表列表,考虑到排除表
例如:
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')
当然我可以查询所有不同的(类型,子类型)并通过结合联合进行另一个查询
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
我正在寻找另一种在单个查询中执行此操作的可靠方法
解决方案
\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
;
推荐阅读
- ruby-on-rails - 如果布局不存在则渲染它
- javascript - 如何通过导出函数将变量从 Jest beforeAll 传递到 Jest 描述
- java - Springboot中加载API定义失败
- php - 每第三个循环后插入一个表
- r - 在 R 中使用行均值和行标准差的列 zscore
- c - C 指针转换
- ios - layoutSubviews() 中的 Flash 滚动指示器
- assembly - 为什么在 cx 中从 4 更改为 8 会多打印一个单词?组装中
- php - 将 Caldera 表单中的某些文件保存到单独的 wordpress 表中
- string - VB.NET System.IO.IOException:句柄无效。将字符串传递给 Web 服务时