如何使用Oracle SQL筛选同时关联欧洲和美洲的记录

要实现仅保留姓名组合（Name+Surname）同时存在Continent为Europe和America的所有记录（即排除仅存在单个大洲的Johnny Bravo），可以用以下几种常用方法：

方法1：GROUP BY 筛选符合条件的姓名组合后关联原表 #

先通过分组统计找出同时拥有两个目标大洲的姓名组合，再关联原表获取所有对应记录：

SELECT t.*
FROM your_table t
JOIN (
    SELECT Name, Surname
    FROM your_table
    GROUP BY Name, Surname
    -- 确保有两个不同的大洲，且恰好是Europe和America
    HAVING COUNT(DISTINCT Continent) = 2 
       AND MAX(Continent) = 'Europe' 
       AND MIN(Continent) = 'America'
) filtered ON t.Name = filtered.Name AND t.Surname = filtered.Surname;

方法2：窗口函数标记筛选 #

用窗口函数给每个姓名组合标记是否包含目标大洲，再过滤出符合条件的记录：

WITH cte AS (
    SELECT 
        *,
        -- 统计当前姓名组合的不同大洲数量
        COUNT(DISTINCT Continent) OVER (PARTITION BY Name, Surname) AS cnt_continent,
        -- 标记是否包含Europe
        MAX(CASE WHEN Continent = 'Europe' THEN 1 ELSE 0 END) OVER (PARTITION BY Name, Surname) AS has_europe,
        -- 标记是否包含America
        MAX(CASE WHEN Continent = 'America' THEN 1 ELSE 0 END) OVER (PARTITION BY Name, Surname) AS has_america
    FROM your_table
)
SELECT Name, Surname, Continent
FROM cte
WHERE cnt_continent = 2 AND has_europe = 1 AND has_america = 1;

方法3：EXISTS 子查询验证 #

通过子查询验证每条记录对应的姓名组合是否存在另一个目标大洲的记录：

SELECT t1.*
FROM your_table t1
WHERE EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.Name = t1.Name 
      AND t2.Surname = t1.Surname 
      -- 检查是否存在对应互补的大洲记录
      AND t2.Continent = CASE WHEN t1.Continent = 'Europe' THEN 'America' ELSE 'Europe' END
);

以上三种方法都能得到预期结果：返回Pier Ruso的两条记录，排除Johnny Bravo的记录。

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