如何用SQL动态筛选首次出现指定国家/地区代码的记录？

原查询通过LIKE 'JP%' OR LIKE '%JP'匹配会出现误判，只要字符串包含目标代码就会被选中，无论它是不是第一个出现的国家代码。我们需要实现的逻辑是：指定国家代码必须是字符串中第一个出现的、属于预设国家代码集合的子串。

核心思路 #

1. 明确国家代码集合（如US、EU、JP、CN等）
2. 为每个字符串定位第一个出现的、属于该集合的代码
3. 验证该代码是否等于目标代码

分数据库实现方案 #

MySQL 8.0+ 实现 #

方法1：用REGEXP_SUBSTR提取首个匹配代码

通过正则匹配独立的国家代码（避免误匹配其他双大写字母组合，如语言代码EN），直接提取第一个匹配项对比目标代码：

-- 设置参数
SET @target_code = 'JP';
SET @country_code_list = 'US,EU,JP,CN'; -- 逗号分隔的国家代码集合

SELECT DISTINCT email_template
FROM table_name
WHERE 
  REGEXP_SUBSTR(
    CONCAT(' ', email_template, ' '), -- 首尾加空格，统一处理开头/结尾的代码
    CONCAT('[^A-Z](', REPLACE(@country_code_list, ',', '|'), ')[^A-Z]'), -- 匹配被非字母包围的国家代码
    1, 1, '', 1 -- 取第一个匹配结果的第1个分组内容
  ) = @target_code;

方法2：检查其他代码的出现位置

通过判断其他国家代码是否出现在目标代码之前，实现精准筛选：

SET @target_code = 'JP';
SET @country_code_list = 'US,EU,JP,CN';

SELECT DISTINCT email_template
FROM table_name
WHERE 
  -- 确保目标代码存在
  email_template REGEXP CONCAT('(^|[^A-Z])', @target_code, '([^A-Z]|$)')
  AND NOT EXISTS (
    -- 排查是否有其他国家代码出现在目标代码之前
    SELECT 1
    FROM (
      -- 拆分国家代码列表为行数据
      SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@country_code_list, ',', n), ',', -1) AS code
      FROM (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) nums
      WHERE n <= LENGTH(@country_code_list) - LENGTH(REPLACE(@country_code_list, ',', '')) + 1
    ) codes
    WHERE code != @target_code
      AND LOCATE(CONCAT(' ', code, ' '), CONCAT(' ', email_template, ' ')) < LOCATE(CONCAT(' ', @target_code, ' '), CONCAT(' ', email_template, ' '))
  );

SQL Server 2017+ 实现 #

方法1：用REGEXP_SUBSTR简化逻辑

DECLARE @target_code NVARCHAR(2) = 'JP';
DECLARE @country_code_list NVARCHAR(100) = 'US,EU,JP,CN';

SELECT DISTINCT email_template
FROM table_name
WHERE 
  REGEXP_SUBSTR(
    CONCAT(' ', email_template, ' '),
    CONCAT('[^A-Z](', REPLACE(@country_code_list, ',', '|'), ')[^A-Z]'),
    1, 1, 0, 1
  ) = @target_code;

方法2：兼容旧版本（无REGEXP_SUBSTR）

用PATINDEX定位代码位置，结合STRING_SPLIT拆分代码列表：

DECLARE @target_code NVARCHAR(2) = 'JP';
DECLARE @country_code_list NVARCHAR(100) = 'US,EU,JP,CN';

SELECT DISTINCT t.email_template
FROM table_name t
WHERE 
  PATINDEX('%[^A-Z]' + @target_code + '[^A-Z]%', CONCAT(' ', t.email_template, ' ')) > 0
  AND NOT EXISTS (
    SELECT 1
    FROM STRING_SPLIT(@country_code_list, ',') c
    WHERE c.value != @target_code
      AND PATINDEX('%[^A-Z]' + c.value + '[^A-Z]%', CONCAT(' ', t.email_template, ' ')) < PATINDEX('%[^A-Z]' + @target_code + '[^A-Z]%', CONCAT(' ', t.email_template, ' '))
  );

PostgreSQL 实现 #

方法1：用substring函数提取匹配项

WITH query_params AS (
  SELECT 
    'JP' AS target_code, 
    ARRAY['US','EU','JP','CN'] AS country_codes
)
SELECT DISTINCT t.email_template
FROM table_name t
CROSS JOIN query_params p
WHERE 
  substring(
    CONCAT(' ', t.email_template, ' '),
    CONCAT('[^A-Z](', array_to_string(p.country_codes, '|'), ')[^A-Z]')
  ) = CONCAT(' ', p.target_code, ' ');

方法2：用regexp_match精准匹配

WITH query_params AS (
  SELECT 
    'JP' AS target_code, 
    ARRAY['US','EU','JP','CN'] AS country_codes
)
SELECT DISTINCT t.email_template
FROM table_name t
CROSS JOIN query_params p
WHERE 
  (regexp_match(CONCAT(' ', t.email_template, ' '), CONCAT('[^A-Z](', array_to_string(p.country_codes, '|'), ')[^A-Z]')))[1] = p.target_code;

动态SQL封装（MySQL存储过程示例） #

如果需要频繁切换目标代码和国家代码集合，可以封装为存储过程：

DELIMITER //
CREATE PROCEDURE GetTemplatesByFirstCountryCode(
  IN p_target_code VARCHAR(2),
  IN p_country_codes VARCHAR(100)
)
BEGIN
  SELECT DISTINCT email_template
  FROM table_name
  WHERE 
    REGEXP_SUBSTR(
      CONCAT(' ', email_template, ' '),
      CONCAT('[^A-Z](', REPLACE(p_country_codes, ',', '|'), ')[^A-Z]'),
      1, 1, '', 1
    ) = p_target_code;
END //
DELIMITER ;

-- 调用示例
CALL GetTemplatesByFirstCountryCode('JP', 'US,EU,JP,CN');

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