如何查询特定年份无预订记录的客户（SQL）

表结构 #

CREATE TABLE tour 
(
    id  bigserial NOT NULL,
    end_date DATE,
    initial_price float8 NOT NULL,
    start_date DATE,
    destination_id int8,
    guide_id int8,

    PRIMARY KEY (id)
);

CREATE TABLE client_data 
(
    id  bigserial NOT NULL,
    name VARCHAR(255),
    passport_number VARCHAR(255),
    surname VARCHAR(255),
    user_data_id int8,

    PRIMARY KEY (id)
);
 
CREATE TABLE reservation 
(
    id bigserial not null,
    actual_price float8 not null,
    client_id int8,
    tour_id int8,

    PRIMARY KEY (id)
);

需求 #

查询特定年份（如2022年）无任何预订记录的客户，包含两类：完全没有过任何预订的客户，以及有其他年份预订但2022年无预订的客户。

遇到的问题 #

现有SQL可查询完全无预订的客户，但添加年份筛选条件后返回0行：

-- 可查询完全无预订的客户
SELECT client_data.name, reservation.id, COUNT(reservation.id)
FROM client_data
LEFT OUTER JOIN reservation ON client_data.id = reservation.client_id
LEFT OUTER JOIN tour ON tour.id = reservation.tour_id
GROUP BY client_data.name, reservation.id
HAVING COUNT(reservation.id) = 0;

-- 同样可查询完全无预订的客户
SELECT client_data.name, reservation.id, COUNT(reservation.id)
FROM client_data
LEFT OUTER JOIN reservation ON client_data.id = reservation.client_id
LEFT OUTER JOIN tour ON tour.id = reservation.tour_id
WHERE reservation.id IS NULL
GROUP BY client_data.name, reservation.id;

当添加WHERE tour.start_date BETWEEN '2022-01-01' AND '2022-12-31'后，查询结果为空。

解决方法 #

方法一：调整JOIN的ON条件（保留左连接逻辑） #

把年份筛选放到LEFT JOIN tour的ON子句中，避免过滤掉主表客户记录：

SELECT 
    cd.id, cd.name, cd.surname, COUNT(r.id) AS 2022年预订数
FROM client_data cd
LEFT JOIN reservation r ON cd.id = r.client_id
LEFT JOIN tour t ON r.tour_id = t.id 
    AND EXTRACT(YEAR FROM t.start_date) = 2022 -- 年份筛选放在JOIN条件里
GROUP BY cd.id, cd.name, cd.surname
HAVING COUNT(r.id) = 0;

逻辑说明：左连接时仅关联2022年的行程预订，统计每个客户在该年份的预订量，量为0的即为目标客户。

方法二：使用NOT EXISTS（逻辑更直观） #

直接判断客户不存在2022年的关联预订记录：

SELECT cd.id, cd.name, cd.surname
FROM client_data cd
WHERE NOT EXISTS (
    SELECT 1
    FROM reservation r
    JOIN tour t ON r.tour_id = t.id
    WHERE r.client_id = cd.id
      AND EXTRACT(YEAR FROM t.start_date) = 2022
);

逻辑说明：遍历每个客户，检查是否存在2022年的行程预订，不存在则返回该客户，性能通常更优。

失败原因解析 #

之前把年份筛选放在WHERE子句中，会过滤掉左连接后t.start_date为NULL的行（即无2022年预订的客户），导致最终无结果返回。而将筛选条件放到JOIN的ON子句中，仅影响关联的行，不会丢弃主表的客户记录。

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