编写SQL查询返回多年预订但不在2021年预订的客户

Question

我需要编写一个查询来回答以下问题:

"提供2018-2020年但2021年未预订的客户报告">

我生成了以下查询，按年分离出如下所示的数据，每个表表示每年的客户预订。

按年获取客户订单:

with active_2018 as
(
select c.id, c.email, c.telephone                       
from customer c                     
join booking b on b.customer_id = c.id                      
where to_char(b.date_created, 'YYYY') = '2018'                      
group by 1, 2, 3                    
),      
active_2019 as 
(
select c.id, c.email, c.telephone                       
from customer c                     
join booking b on b.customer_id = c.id                      
where to_char(b.date_created, 'YYYY') = '2019'                      
group by 1, 2, 3                    
),  
active_2020 as 
(
select c.id, c.email, c.telephone                       
from customer c                     
join booking b on b.customer_id = c.id                      
where to_char(b.date_created, 'YYYY') = '2020'                      
group by 1, 2, 3                    
),
active_2021 as 
(
select c.id, c.email, c.telephone                       
from customer c                     
join booking b on b.customer_id = c.id                      
where to_char(b.date_created, 'YYYY') = '2021'                      
group by 1, 2, 3                    
)

我意识到下面的尝试是错误的，但到目前为止，这是我最好的尝试，我认为这个查询是说显示在2018年、2019年和2020年预订的客户预订，而不是在2021年。这是错误的，因为我实际上需要的是在2018年或2019年或2020年预订的客户，而不是在2021年。

尝试#1 -失败:

select distinct(a.id), a.email, a.telephone     
from active_2018 a on a.id = a.id 
join active_2019 b on a.id = b.id   
join active_2020 c on a.id = c.id           
where a.id not in (select id from active_2021)      

Answer 1

edit (again..]):在下面评论和查看文档之后。你真的需要一个工会。因为它们可能在2018年、2019年或2020年，但不是2021年。"or"这就是为什么我认为你需要一个工会，而不是加入。除了2021年，没有人在每一年都预订过。

'
select distinct(a.id) as id, a.email as email, a.telephone as telephone     
from active_2018
where a.id not in (select id from active_2021)
union
select distinct(b.id) as id, b.email as email, b.telephone as telephone     
from active_2018 as b
where b.id not in (select id from active_2021)
union
select distinct(c.id) as id, c.email as email, c.telephone as telephone     
from active_2018 as c
where c.id not in (select id from active_2021);               

'