SQL查询开发需求：验证对象B版本始终≤对象A版本（兼容特殊序列）

嘿，Kevin，这个版本号比较的坑我之前也踩过！尤其是这种Z99之后跳AA0的特殊序列，常规的字符串比较完全不管用——毕竟字符串里'AA0'确实会被判定为小于'H08'（因为A的ASCII码比H小）。我给你几个实用的解决方案，你可以根据自己的数据库环境和需求来选：

核心思路是把这种字母+数字的版本号转换成能直接比较的数值。比如把字母部分转成类似Excel列名的数字（A=1，Z=26，AA=27，AB=28...），数字部分转成整数，再把两者组合成一个大数值，这样就能用常规的<=来比较了。

以MySQL为例，你可以创建这样一个函数：

CREATE FUNCTION convert_version_to_num(version VARCHAR(10)) 
RETURNS BIGINT
DETERMINISTIC
BEGIN
    DECLARE letter_part VARCHAR(10);
    DECLARE num_part VARCHAR(10);
    DECLARE letter_num BIGINT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    DECLARE char_val INT;
    
    -- 拆分字母和数字部分（用正则适配不同长度的字母/数字）
    SET letter_part = REGEXP_SUBSTR(version, '^[A-Z]+');
    SET num_part = REGEXP_SUBSTR(version, '[0-9]+$');
    
    -- 把字母部分转成数值（比如AA → 27）
    WHILE i <= LENGTH(letter_part) DO
        SET char_val = ASCII(UPPER(SUBSTRING(letter_part, i, 1))) - ASCII('A') + 1;
        SET letter_num = letter_num * 26 + char_val;
        SET i = i + 1;
    END WHILE;
    
    -- 组合成可比较的数值（假设数字最多3位，乘1000足够容纳）
    RETURN letter_num * 1000 + CAST(num_part AS UNSIGNED);
END;

之后验证查询就可以这么写：

SELECT 
    a.object_id,
    a.version AS a_version,
    b.version AS b_version,
    CASE 
        WHEN convert_version_to_num(b.version) <= convert_version_to_num(a.version) THEN '合规'
        ELSE '异常'
    END AS validation_result
FROM 
    table_a a
JOIN 
    table_b b ON a.object_id = b.object_id;

如果不想新增函数，也可以在查询语句里直接拆分版本号进行比较，逻辑和上面一致：

SELECT 
    a.object_id,
    a.version AS a_version,
    b.version AS b_version,
    CASE
        -- 先比字母部分的"大小"
        WHEN REGEXP_SUBSTR(a.version, '^[A-Z]+') > REGEXP_SUBSTR(b.version, '^[A-Z]+') THEN '合规'
        WHEN REGEXP_SUBSTR(a.version, '^[A-Z]+') < REGEXP_SUBSTR(b.version, '^[A-Z]+') THEN '异常'
        -- 字母部分相等时，再比数字部分
        ELSE CASE WHEN CAST(REGEXP_SUBSTR(a.version, '[0-9]+$') AS UNSIGNED) >= CAST(REGEXP_SUBSTR(b.version, '[0-9]+$') AS UNSIGNED) THEN '合规' ELSE '异常' END
    END AS validation_result
FROM 
    table_a a
JOIN 
    table_b b ON a.object_id = b.object_id;

⚠️ 小提示：如果你的版本号格式是固定的（比如字母都是1位或2位，数字都是2位），也可以用LEFT()/RIGHT()替代正则，性能会稍好一点，但正则的适配性更强。

如果你的数据量很大，或者需要频繁做这个验证，建议在表中新增一个version_sort字段，每次插入/更新版本号时自动计算并存储转换后的数值。这样查询时直接用这个字段比较，性能会提升很多。

比如插入数据时：

INSERT INTO table_a (object_id, version, version_sort)
VALUES ('your_id', 'AA0', convert_version_to_num('AA0'));

验证查询就变得非常简洁：

SELECT 
    a.object_id,
    a.version AS a_version,
    b.version AS b_version,
    CASE WHEN b.version_sort <= a.version_sort THEN '合规' ELSE '异常' END AS validation_result
FROM 
    table_a a
JOIN 
    table_b b ON a.object_id = b.object_id;

最后提醒一句：要确保所有版本号的格式是统一的（比如都是大写字母+数字，没有混合小写或特殊字符），这样上面的方法才能稳定工作哦！

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