如何校验trip_locations表中指定行程的记录组合并支持两种查询场景？

现有表结构 #

CREATE TABLE trip_locations(
    trip_id     int NOT NULL,
    city_id     bigint,
    country_id  smallint,
    
    FOREIGN KEY (trip_id) references trips (id) ON DELETE CASCADE,
    FOREIGN KEY (city_id) references cities (id) ON DELETE CASCADE,
    FOREIGN KEY (country_id) references countries (id) ON DELETE CASCADE
);

需求场景 #

针对指定trip_id（示例为123）实现两种存在性校验：

• 场景1：忽略country_id，仅校验指定的city_id是否存在于对应trip的记录中
• 场景2：校验组合存在性：若条目包含country_id，则必须存在同时匹配city_id和country_id的记录；若条目仅含country_id，则校验该country_id存在；若条目仅含city_id，则同场景1逻辑

原查询的问题 #

你提供的原查询使用OR关联条件，会导致匹配逻辑错误：

1. 单条trip_locations记录可能同时匹配多个must_exist条目，导致计数重复，误判为所有条目都存在
2. 无法精准区分“单个字段匹配”和“组合字段匹配”的校验逻辑，返回结果不符合预期

正确实现方案 #

场景1：仅校验city_id存在 #

使用双层NOT EXISTS确保所有指定的city_id都在目标trip的记录中存在：

WITH must_exist (city_id) AS (VALUES (4010), (/* 其他city_id */))
SELECT NOT EXISTS (
    SELECT 1
    FROM must_exist
    WHERE NOT EXISTS (
        SELECT 1
        FROM trip_locations
        WHERE trip_id = 123
          AND city_id = must_exist.city_id
    )
) AS result;

• 逻辑：只要有一个指定的city_id不存在，就返回false；全部存在则返回true

场景2：校验组合存在性 #

针对不同条目类型（仅city_id、仅country_id、city+country组合）精准匹配：

WITH must_exist (city_id, country_id) AS (VALUES 
    (4010, 193), -- 需要同时匹配city和country的记录
    (NULL, 193), -- 需要匹配country的记录
    (5020, NULL) -- 需要匹配city的记录
)
SELECT NOT EXISTS (
    SELECT 1
    FROM must_exist
    WHERE NOT EXISTS (
        SELECT 1
        FROM trip_locations
        WHERE trip_id = 123
          -- 仅当must_exist的city_id非空时，匹配city_id
          AND (must_exist.city_id IS NULL OR city_id = must_exist.city_id)
          -- 仅当must_exist的country_id非空时，匹配country_id
          AND (must_exist.country_id IS NULL OR country_id = must_exist.country_id)
          -- 确保至少匹配一个字段（因为表中每行至少有一个字段非空）
          AND (city_id IS NOT NULL OR country_id IS NOT NULL)
    )
) AS result;

• 逻辑：对每个must_exist条目，检查是否存在符合条件的trip_locations记录；只要有一个条目无匹配，返回false，否则返回true

补充说明 #

如果需要兼容“后续表中可能同时存在city_id和country_id”的情况，上述方案无需修改，逻辑已经覆盖了单字段和组合字段的匹配场景。

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