如何编写SQL查询获取2018-2020年有预订但2021年无预订的客户

需求定义 #

输出2018年至2020年期间有过预订、但2021年没有任何预订的客户报表

现有基础代码（按年份拆分的活跃客户CTE） #

你已经编写的按年份拆分年度活跃客户的CTE可以直接复用：

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                   
)

原有错误写法问题说明 #

你首次尝试的写法用inner join关联三个年度的活跃客户表，只有同时在2018、2019、2020三年都有预订的客户才会被关联保留，不符合「任意一年有预订即可」的要求。

正确实现方案 #

方案1：基于现有CTE直接修改 #

用UNION合并2018-2020三年的活跃客户，再排除2021年有预订的客户即可：

select id, email, telephone
from (
    select id, email, telephone from active_2018
    union
    select id, email, telephone from active_2019
    union
    select id, email, telephone from active_2020
) active_2018to2020
where id not in (select id from active_2021)

• UNION会自动对合并的客户数据去重，不需要额外加distinct
• 逻辑和需求完全匹配：只要2018-2020任意一年有预订，且2021年无预订就会被保留

方案2：更简洁的优化写法（无需拆分多个CTE） #

如果不需要单独保留各年度的CTE，可直接一次聚合统计，性能更优：

select c.id, c.email, c.telephone
from customer c
join booking b on b.customer_id = c.id
group by c.id, c.email, c.telephone
having 
    -- 判断2018-2020至少有1笔预订
    max(case when to_char(b.date_created, 'YYYY') between '2018' and '2020' then 1 else 0 end) = 1
    -- 判断2021年无任何预订
    and max(case when to_char(b.date_created, 'YYYY') = '2021' then 1 else 0 end) = 0

内容的提问来源于stack exchange，提问作者James