嘿，这个需求我之前帮人解决过不少次——把这种混合了单个值和范围的字符串转换成IN子句能用的数字集合，确实需要分几步处理。我给你分常见的两种数据库场景写具体实现：

MySQL 8.0及以上支持递归CTE，刚好可以用来拆分字符串并展开范围：

WITH RECURSIVE split_range AS (
    -- 第一步：拆分分号分隔的字符串，得到单个项（比如1、2、3、6-9）
    SELECT SUBSTRING_INDEX(range, ';', 1) AS item,
           SUBSTRING(range, LENGTH(SUBSTRING_INDEX(range, ';', 1)) + 2) AS remaining
    FROM my_table
    UNION ALL
    SELECT SUBSTRING_INDEX(remaining, ';', 1) AS item,
           SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ';', 1)) + 2) AS remaining
    FROM split_range
    WHERE remaining != ''
),
expand_range AS (
    -- 第二步：把每个项拆成起始和结束数字（单个值的话起始=结束）
    SELECT 
        CASE WHEN INSTR(item, '-') > 0 THEN SUBSTRING_INDEX(item, '-', 1) ELSE item END AS start_num,
        CASE WHEN INSTR(item, '-') > 0 THEN SUBSTRING_INDEX(item, '-', -1) ELSE item END AS end_num
    FROM split_range
),
generate_numbers AS (
    -- 第三步：生成所有需要的数字，包括单个值和范围展开后的连续数字
    SELECT CAST(start_num AS UNSIGNED) AS num
    FROM expand_range
    WHERE start_num = end_num
    UNION ALL
    SELECT gn.num + 1
    FROM generate_numbers gn
    JOIN expand_range er ON gn.num < CAST(er.end_num AS UNSIGNED)
    WHERE gn.num >= CAST(er.start_num AS UNSIGNED)
)
-- 最后用生成的数字集合做IN查询
SELECT id 
FROM my_another_table 
WHERE id IN (SELECT num FROM generate_numbers);

PostgreSQL有更便捷的字符串拆分和序列生成函数，写法会简洁很多：

WITH split_range AS (
    -- 拆分分号分隔的字符串，得到单个项
    SELECT unnest(string_to_array(range, ';')) AS item
    FROM my_table
),
expand_range AS (
    -- 拆分每个项为起始和结束数字
    SELECT 
        CASE WHEN strpos(item, '-') > 0 THEN split_part(item, '-', 1)::INT ELSE item::INT END AS start_num,
        CASE WHEN strpos(item, '-') > 0 THEN split_part(item, '-', 2)::INT ELSE item::INT END AS end_num
    FROM split_range
),
generate_numbers AS (
    -- 直接用generate_series生成范围里的所有数字
    SELECT generate_series(start_num, end_num) AS num
    FROM expand_range
)
-- 执行IN查询
SELECT id 
FROM my_another_table 
WHERE id IN (SELECT num FROM generate_numbers);

• 如果你的字符串里可能存在非数字内容，记得用容错转换函数：MySQL用TRY_CAST替代CAST，PostgreSQL可以用safe_cast（需要扩展）或者加异常处理；
• 如果原字符串里有重复值，记得在generate_numbers的查询里加DISTINCT，比如SELECT DISTINCT num FROM generate_numbers；
• 要是用的是MySQL 5.x这类不支持递归CTE的老版本，得先创建一个数字辅助表（比如从1到1000的数字表），再关联拆分字符串和生成范围。

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