如何在同一列中多模式匹配，查询存在于多城市的用户记录

示例表格 #

需求 #

需要查询仅存在于2个或以上城市的姓名（本数据集最多为2个城市），预期结果仅返回Sarah Phillips。

遇到的问题 #

之前编写的查询使用了like NewYork% or like Boston%运算符，配合having count(location) > 1，但会错误返回John Smith——因为NewYork后带不同后缀的记录会被算作多条，实际属于同一城市。

解决方案 #

核心是先从Location字段中提取出真实的城市名称，再统计每个姓名对应的不同城市数量是否达标。

方案1：针对已知城市的判断 #

如果只有NewYork和Boston两种城市，可以用CASE语句直接映射：

SELECT Name
FROM (
    SELECT 
        Name,
        CASE 
            WHEN Location LIKE 'NewYork%' THEN 'NewYork'
            WHEN Location LIKE 'Boston%' THEN 'Boston'
            ELSE Location
        END AS City
    FROM your_table_name
) AS city_extracted
GROUP BY Name
HAVING COUNT(DISTINCT City) >= 2;

方案2：通用正则提取 #

如果城市名是纯字母开头，后面跟数字后缀，用正则提取字母部分：

SELECT Name
FROM (
    SELECT 
        Name,
        REGEXP_SUBSTR(Location, '^[A-Za-z]+') AS City
    FROM your_table_name
) AS city_extracted
GROUP BY Name
HAVING COUNT(DISTINCT City) >= 2;

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