基于特定位置字符的查找表决策咨询（标题可优化）

Hey there! 作为同样搞前后端开发、偶尔要啃数据库业务规则的工程师，我完全懂你这种非专职DBA但要搞定产品编码规范的处境😉 结合你用T-SQL管理配送中心数据的场景，给你分享几个实用的查找表设计方案，适配不同的编码规则复杂度：

不管编号有多少位，核心都是把每个位置的编码值和对应的业务含义存在独立的查找表里，既方便维护规则，也能在查询时快速解析编码的意义。

方案1：分表存储独立的编号位规则 #

适合每个编号位的含义完全独立（比如第3位类别、第4位异常情况互相不关联）的场景，好处是结构清晰、扩展简单。

比如针对你的需求，可以建两张基础表，后续新增第5、6位规则时直接加表就行：

-- 第3位：类别映射表
CREATE TABLE ProductCodeCategory (
    CategoryCode CHAR(1) PRIMARY KEY, -- 存储编号里的单字符码，比如'A'/'B'
    CategoryName NVARCHAR(50) NOT NULL, -- 对应的类别名称，比如"生鲜食品"
    IsActive BIT DEFAULT 1 NOT NULL -- 标记该编码是否还在使用，方便淘汰旧规则
);

-- 插入示例数据
INSERT INTO ProductCodeCategory (CategoryCode, CategoryName)
VALUES ('A', '生鲜食品'), ('B', '日化用品'), ('C', '家居家电');

-- 第4位：异常情况映射表
CREATE TABLE ProductCodeException (
    ExceptionCode CHAR(1) PRIMARY KEY,
    ExceptionDesc NVARCHAR(100) NOT NULL, -- 异常描述，比如"临期产品"
    IsActive BIT DEFAULT 1 NOT NULL
);

INSERT INTO ProductCodeException (ExceptionCode, ExceptionDesc)
VALUES ('0', '无异常'), ('1', '临期产品'), ('2', '包装破损');

解析编码时的用法 #

比如要解析产品编号PD-A1-001的含义：

DECLARE @ProductCode NVARCHAR(20) = 'PD-A1-001';
-- 提取对应位置的编码
DECLARE @CategoryCode CHAR(1) = SUBSTRING(@ProductCode, 3, 1);
DECLARE @ExceptionCode CHAR(1) = SUBSTRING(@ProductCode, 4, 1);

-- 关联查找表获取业务含义
SELECT 
    pc.CategoryName AS 产品类别,
    pe.ExceptionDesc AS 异常状态
FROM 
    (SELECT @CategoryCode AS CategoryCode) AS c
LEFT JOIN ProductCodeCategory pc ON c.CategoryCode = pc.CategoryCode
LEFT JOIN ProductCodeException pe ON @ExceptionCode = pe.ExceptionCode;

方案2：单表统一管理所有编号位规则 #

如果编号位之间有联动（比如某些类别下的异常码含义不同），或者想统一维护所有规则，可以用一张表加Position字段标记对应编号位：

CREATE TABLE ProductCodeRule (
    RuleID INT IDENTITY(1,1) PRIMARY KEY,
    Position INT NOT NULL, -- 标记属于编号的第几位，比如3、4、5
    Code CHAR(1) NOT NULL, -- 编号里的单字符码
    CodeDesc NVARCHAR(100) NOT NULL, -- 对应的业务含义
    ParentCode CHAR(1) NULL, -- 可选：如果当前位的码依赖前一位的编码（比如日化类的'1'是瓶盖松动，生鲜类的'1'是临期），存前一位的码
    IsActive BIT DEFAULT 1 NOT NULL,
    -- 确保同一位置、同一父码下的编码唯一
    UNIQUE(Position, Code, ParentCode)
);

-- 插入示例数据（包含联动规则）
INSERT INTO ProductCodeRule (Position, Code, CodeDesc, ParentCode)
VALUES 
(3, 'A', '生鲜食品', NULL),
(3, 'B', '日化用品', NULL),
(4, '0', '无异常', NULL),
(4, '1', '临期产品', 'A'), -- 生鲜类的1代表临期
(4, '1', '瓶盖松动', 'B'); -- 日化类的1代表瓶盖松动

解析联动编码的用法 #

比如解析编号PD-B1-001的异常含义：

DECLARE @ProductCode NVARCHAR(20) = 'PD-B1-001';
DECLARE @Position3Code CHAR(1) = SUBSTRING(@ProductCode, 3, 1);
DECLARE @Position4Code CHAR(1) = SUBSTRING(@ProductCode, 4, 1);

-- 优先取对应类别下的特定规则，没有则取通用规则
SELECT CodeDesc AS 异常状态
FROM ProductCodeRule
WHERE Position = 4 
AND Code = @Position4Code
AND (ParentCode IS NULL OR ParentCode = @Position3Code)
ORDER BY ParentCode DESC;

T-SQL场景下的额外小技巧 #

• 给查找表的编码字段加主键/索引：比如ProductCodeCategory的CategoryCode作为主键，查询时能大幅提升速度，尤其是数据量上来之后。
• 写校验函数避免无效编码：可以封装一个函数，自动校验新生成的产品编号是否符合查找表规则：

CREATE FUNCTION dbo.ValidateProductCode(@Code NVARCHAR(20))
RETURNS BIT
AS BEGIN
    DECLARE @Valid BIT = 1;
    -- 校验第3位类别码是否有效
    IF NOT EXISTS (SELECT 1 FROM ProductCodeCategory WHERE CategoryCode = SUBSTRING(@Code,3,1) AND IsActive=1)
        SET @Valid = 0;
    -- 校验第4位异常码是否有效
    IF NOT EXISTS (SELECT 1 FROM ProductCodeException WHERE ExceptionCode = SUBSTRING(@Code,4,1) AND IsActive=1)
        SET @Valid = 0;
    RETURN @Valid;
END;

• 用IsActive字段淘汰旧规则：不用直接删除历史编码数据，标记为无效即可，方便追溯旧产品的编码含义。

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