SQL如何匹配字符串格式范围列 实现两表关联查询对应类型

实现思路 #

你需要先实现范围匹配逻辑，ranges字段规则为：用逗号拆分出多个子规则，每个子规则如果包含-就是闭区间，否则是需要精确匹配的离散值。你可以把匹配逻辑封装成自定义函数，再关联两张表查询即可。

MySQL 实现示例 #

第一步：创建自定义匹配函数

DELIMITER //
CREATE FUNCTION IS_VALUE_WITHIN_RANGE(p_value INT, p_ranges VARCHAR(255))
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE v_range VARCHAR(255);
    DECLARE v_pos INT;
    DECLARE v_start INT;
    DECLARE v_end INT;
    DECLARE v_comma_pos INT;
    
    -- 循环拆分逗号分隔的多个规则段
    WHILE LENGTH(p_ranges) > 0 DO
        SET v_comma_pos = INSTR(p_ranges, ',');
        IF v_comma_pos = 0 THEN
            SET v_range = p_ranges;
            SET p_ranges = '';
        ELSE
            SET v_range = LEFT(p_ranges, v_comma_pos - 1);
            SET p_ranges = SUBSTRING(p_ranges, v_comma_pos + 1);
        END IF;
        
        -- 匹配当前规则段
        SET v_pos = INSTR(v_range, '-');
        IF v_pos = 0 THEN
            -- 离散值精确匹配
            IF CAST(v_range AS UNSIGNED) = p_value THEN
                RETURN TRUE;
            END IF;
        ELSE
            -- 区间范围匹配
            SET v_start = CAST(LEFT(v_range, v_pos - 1) AS UNSIGNED);
            SET v_end = CAST(SUBSTRING(v_range, v_pos + 1) AS UNSIGNED);
            IF p_value BETWEEN v_start AND v_end THEN
                RETURN TRUE;
            END IF;
        END IF;
    END WHILE;
    
    RETURN FALSE;
END //
DELIMITER ;

第二步：执行关联查询

直接用你提到的关联逻辑即可：

SELECT t2.id, t1.type
FROM table2 t2
LEFT JOIN table1 t1 ON IS_VALUE_WITHIN_RANGE(t2.value, t1.ranges);

用你给出的示例数据执行上述查询，就能得到预期结果：

注意事项 #

• 如果存在单个value匹配多个type的情况，可以根据需求加DISTINCT去重，或者加排序规则取优先级最高的type
• 以上是MySQL的实现，其他数据库（比如PostgreSQL、Oracle）可以用对应的字符串处理函数改写匹配逻辑，核心思路一致
• 如果表数据量较大，建议提前清洗ranges字段拆成独立的区间表存储，避免每次查询都做字符串拆分，性能更好

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