如何简化多列匹配加拿大城市的SQL标记列生成逻辑？

需求回顾：需新增Canadian_City_Visit列，当First_City_Visit/Second_City_Visit/Third_City_Visit/Fourth_City_Visit任意一列属于Toronto、Ottawa、Montreal、Vancouver、Calgary时赋值1，否则0。原代码在列数增多、城市数量庞大时过于繁琐，以下是几种简洁实现方式：

方法1：利用行构造函数+EXISTS（兼容多数数据库） #

将多列打包成临时行集合，只需声明一次城市列表即可完成匹配检查：

SELECT 
    Name, 
    First_City_Visit, 
    Second_City_Visit, 
    Third_City_Visit, 
    Fourth_City_Visit,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM (VALUES (First_City_Visit), (Second_City_Visit), (Third_City_Visit), (Fourth_City_Visit)) AS t(city)
            WHERE t.city IN ('Toronto', 'Ottawa', 'Montreal', 'Vancouver', 'Calgary')
        ) THEN 1
        ELSE 0
    END AS Canadian_City_Visit
FROM my_table

优点：列数增加时仅需在VALUES中追加新列；城市列表仅写一次，后续修改更便捷。

方法2：UNION ALL转列成行+EXISTS #

通过子查询把多列转为单行多值结构，再匹配目标城市：

SELECT 
    m.Name, 
    m.First_City_Visit, 
    m.Second_City_Visit, 
    m.Third_City_Visit, 
    m.Fourth_City_Visit,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM (
                SELECT m.First_City_Visit UNION ALL
                SELECT m.Second_City_Visit UNION ALL
                SELECT m.Third_City_Visit UNION ALL
                SELECT m.Fourth_City_Visit
            ) AS t(city)
            WHERE t.city IN ('Toronto', 'Ottawa', 'Montreal', 'Vancouver', 'Calgary')
        ) THEN 1
        ELSE 0
    END AS Canadian_City_Visit
FROM my_table m

优点：逻辑通用，几乎适配所有SQL数据库；列数扩展时仅需添加UNION ALL语句。

方法3：创建辅助表（适配超大规模城市列表） #

如果目标城市超过100个，建议单独维护城市列表表，后续无需修改主查询：

1. 先创建并填充辅助表：

CREATE TABLE canadian_cities (city_name VARCHAR(100) PRIMARY KEY);
INSERT INTO canadian_cities (city_name) 
VALUES ('Toronto'), ('Ottawa'), ('Montreal'), ('Vancouver'), ('Calgary');

2. 主查询关联辅助表判断：

SELECT 
    m.Name, 
    m.First_City_Visit, 
    m.Second_City_Visit, 
    m.Third_City_Visit, 
    m.Fourth_City_Visit,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM (
                SELECT m.First_City_Visit UNION ALL
                SELECT m.Second_City_Visit UNION ALL
                SELECT m.Third_City_Visit UNION ALL
                SELECT m.Fourth_City_Visit
            ) AS t(city)
            JOIN canadian_cities cc ON t.city = cc.city_name
        ) THEN 1
        ELSE 0
    END AS Canadian_City_Visit
FROM my_table m

优点：城市增删改仅需操作辅助表，主查询逻辑固定，适合长期维护的大规模场景。

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