TSQL中为200亿行MSSQL表生成车牌0/O全排列构建WHERE IN子句

针对200亿行的大表，不能用REPLACE（会触发全表扫描），需要生成目标车牌所有0/O位置的替换组合，用精确匹配的WHERE IN来利用索引，具体实现如下：

核心思路 #

找出目标车牌中所有是0或O的位置，为每个位置生成0和O两种可能，组合出所有候选车牌值，最终用这些值做IN子句的精确匹配，这样能触发车牌列的索引扫描，大幅提升查询效率。

TSQL实现代码 #

以下代码以目标车牌'0OABC'为例，生成所有4种组合：

DECLARE @TargetPlate NVARCHAR(20) = '0OABC';

-- 递归CTE生成所有0/O替换组合
WITH PlateCTE AS (
    -- 初始化：拆分第一个字符
    SELECT 
        CAST(CASE WHEN SUBSTRING(@TargetPlate, 1, 1) IN ('0', 'O') THEN '0' ELSE SUBSTRING(@TargetPlate, 1, 1) END AS NVARCHAR(20)) AS Plate,
        CAST(CASE WHEN SUBSTRING(@TargetPlate, 1, 1) IN ('0', 'O') THEN 'O' ELSE SUBSTRING(@TargetPlate, 1, 1) END AS NVARCHAR(20)) AS AltPlate,
        1 AS Position
    UNION ALL
    -- 递归处理后续每个字符
    SELECT
        CAST(p.Plate + CASE WHEN SUBSTRING(@TargetPlate, c.Position + 1, 1) IN ('0', 'O') THEN '0' ELSE SUBSTRING(@TargetPlate, c.Position + 1, 1) END AS NVARCHAR(20)),
        CAST(p.Plate + CASE WHEN SUBSTRING(@TargetPlate, c.Position + 1, 1) IN ('0', 'O') THEN 'O' ELSE SUBSTRING(@TargetPlate, c.Position + 1, 1) END AS NVARCHAR(20)),
        c.Position + 1
    FROM PlateCTE c
    CROSS APPLY (
        VALUES (c.Plate), (c.AltPlate)
    ) p(Plate)
    WHERE c.Position < LEN(@TargetPlate)
),
-- 收集所有唯一的组合
AllPlates AS (
    SELECT DISTINCT Plate FROM PlateCTE
    UNION
    SELECT DISTINCT AltPlate FROM PlateCTE
)
-- 生成最终查询的IN子句值，或者直接用于查询
SELECT Plate FROM AllPlates;

执行后会输出：

00ABC
0OABC
OOABC
O0ABC

直接用于查询的方式 #

如果要直接查询表，可把生成的组合作为IN的参数：

DECLARE @TargetPlate NVARCHAR(20) = '000XYZ';

WITH PlateCTE AS (
    SELECT 
        CAST(CASE WHEN SUBSTRING(@TargetPlate, 1, 1) IN ('0', 'O') THEN '0' ELSE SUBSTRING(@TargetPlate, 1, 1) END AS NVARCHAR(20)) AS Plate,
        CAST(CASE WHEN SUBSTRING(@TargetPlate, 1, 1) IN ('0', 'O') THEN 'O' ELSE SUBSTRING(@TargetPlate, 1, 1) END AS NVARCHAR(20)) AS AltPlate,
        1 AS Position
    UNION ALL
    SELECT
        CAST(p.Plate + CASE WHEN SUBSTRING(@TargetPlate, c.Position + 1, 1) IN ('0', 'O') THEN '0' ELSE SUBSTRING(@TargetPlate, c.Position + 1, 1) END AS NVARCHAR(20)),
        CAST(p.Plate + CASE WHEN SUBSTRING(@TargetPlate, c.Position + 1, 1) IN ('0', 'O') THEN 'O' ELSE SUBSTRING(@TargetPlate, c.Position + 1, 1) END AS NVARCHAR(20)),
        c.Position + 1
    FROM PlateCTE c
    CROSS APPLY (
        VALUES (c.Plate), (c.AltPlate)
    ) p(Plate)
    WHERE c.Position < LEN(@TargetPlate)
),
AllPlates AS (
    SELECT DISTINCT Plate FROM PlateCTE
    UNION
    SELECT DISTINCT AltPlate FROM PlateCTE
)
SELECT * FROM plates
WHERE plate IN (SELECT Plate FROM AllPlates);

关键优势 #

• 避免全表扫描：IN子句的精确匹配可以利用车牌列的非聚集索引，对于200亿行的大表，索引扫描的效率远高于REPLACE导致的表扫描。
• 覆盖所有可能：递归CTE会生成所有0/O位置的替换组合，不会遗漏任何可能的误识别情况。

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