SQL查询如何组合WHERE与HAVING条件实现field_region字段搜索

现有SQL无法支持field_region字段搜索、且不能直接在WHERE和HAVING之间拼接OR逻辑的核心原因：

• SQL执行顺序中，WHERE子句执行在SELECT子句之前，无法识别SELECT段中才通过计算生成的field_region别名
• field_region是关联子查询拼接生成的字符串，不属于当前GROUP BY的分组维度，直接在HAVING中引用该别名会触发语法错误
• 如果把field_region的拼接逻辑重复写在WHERE条件中，会导致子查询重复执行，性能损耗大且代码难以维护

提供两种生产环境可用的写法，可根据业务场景选择：

方案1：CTE预计算（可读性优先） #

先通过公用表表达式提前计算出所有行的field_region字段，再在外层查询统一做三个字段的OR匹配，逻辑直观易维护，适合数据量中等的场景：

WITH user_sg_full AS (
    SELECT 
        user_sg.id AS id,
        user_sg.name AS name,
        id_channel,
        master_channel.code,
        master_channel.name AS m_name,
        user_sg.id_relation,
        STUFF((SELECT ', ' + region_code
               FROM user_sg_region AS T3 
               WHERE T3.id_sg = user_sg.id 
               FOR XML PATH('')), 1, 2, '') AS field_region
    FROM user_sg 
    INNER JOIN master_channel ON user_sg.id_channel = master_channel.id 
    GROUP BY user_sg.id, user_sg.name, id_channel, master_channel.code, master_channel.name, user_sg.id_relation
)
SELECT * FROM user_sg_full
WHERE 
    name LIKE '%search%' 
    OR m_name LIKE '%search%'
    OR field_region LIKE '%search%'
ORDER BY Id
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

方案2：EXISTS提前过滤（性能优先） #

不需要提前拼接全量数据的region字符串，直接在WHERE阶段通过EXISTS判断关联的user_sg_region表中是否存在匹配关键词的编码，匹配即返回对应行，大数据量下执行效率远高于方案1：

SELECT 
    user_sg.id AS id,
    user_sg.name AS name,
    id_channel,
    master_channel.code,
    master_channel.name AS m_name,
    user_sg.id_relation,
    STUFF((SELECT ', ' + region_code
               FROM user_sg_region AS T3 
               WHERE T3.id_sg = user_sg.id 
              FOR XML PATH('')), 1, 2, '') AS field_region
FROM user_sg 
INNER JOIN master_channel ON user_sg.id_channel = master_channel.id 
WHERE 
    user_sg.name LIKE '%search%' 
    OR master_channel.name LIKE '%search%'
    OR EXISTS (
        SELECT 1 
        FROM user_sg_region r 
        WHERE r.id_sg = user_sg.id AND r.region_code LIKE '%search%'
    )
GROUP BY user_sg.id, user_sg.name, id_channel, master_channel.code, master_channel.name, user_sg.id_relation
ORDER BY user_sg.Id
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

注意：代码中的%search%占位符需要替换为实际传入的用户搜索关键词，生产环境建议使用参数化查询传入关键词，不要直接拼接SQL字符串，避免SQL注入风险。

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