如何用SQL结合列表实现数据表列的参考编号部分匹配？

针对你手里的参考编号缺少最后一位、无法用IN子句匹配数据表中完整编号的问题，以下是几种高效的批量解决方案：

方案1：子查询+LEFT函数关联（通用且高效） #

把你的参考编号列表封装成子查询（或临时表），通过LEFT函数提取数据表中编号的前缀进行匹配，完美对应“少一位”的场景：

-- 用WITH子句定义参考编号列表（也可换成临时表）
WITH your_reference_list AS (
    SELECT 'abc 123' AS partial_ref
    UNION ALL SELECT 'abc 456'
    UNION ALL SELECT 'abc 789'
    -- 继续添加所有需要匹配的编号
)
SELECT t.*
FROM "table" t
JOIN your_reference_list r 
  ON LEFT(t."reference_number", LENGTH(r.partial_ref)) = r.partial_ref;

这种方式性能最优，尤其是当参考编号数量极多时，数据库可通过关联逻辑高效匹配，避免大量OR条件的性能损耗。

方案2：LIKE + ANY操作符（部分数据库支持） #

如果你的数据库支持ANY数组操作（比如PostgreSQL），可以给每个参考编号添加%通配符，批量匹配前缀：

SELECT *
FROM "table"
WHERE "reference_number" LIKE ANY (
    ARRAY[
        'abc 123%',
        'abc 456%',
        'abc 789%'
        -- 每个编号末尾加%，匹配任意结尾（此处刚好对应多一位数字）
    ]
);

MySQL可通过EXISTS结合CONCAT实现类似效果：

SELECT *
FROM "table" t
WHERE EXISTS (
    SELECT 1 
    FROM (
        SELECT 'abc 123' AS partial_ref UNION ALL
        SELECT 'abc 456' UNION ALL
        SELECT 'abc 789'
    ) AS refs
    WHERE t."reference_number" LIKE CONCAT(refs.partial_ref, '%')
);

方案3：正则表达式匹配（精准控制结尾格式） #

如果需要严格匹配“末尾多一位数字”的规则，可使用正则表达式，不同数据库语法略有差异：

MySQL/MariaDB： #

SELECT *
FROM "table"
WHERE "reference_number" REGEXP '^(abc 123|abc 456|abc 789)[0-9]$';

PostgreSQL： #

SELECT *
FROM "table"
WHERE "reference_number" ~ '^(abc 123|abc 456|abc 789)[0-9]$';

正则表达式[0-9]$确保结尾只有一位数字，完全匹配你的数据表编号格式。

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