如何从VARCHAR类型列的每条记录中提取最小数值？

针对你的需求（从VARCHAR类型的逗号分隔数字列中提取每条记录的最小值），以下是不同主流数据库的具体实现方案：

MySQL 8.0+ 方案 #

利用递归CTE拆分字符串，再分组求最小值：

WITH RECURSIVE split_ranks AS (
    SELECT 
        id,  -- 假设表有主键id用于关联原记录
        Ranks AS remaining,
        SUBSTRING_INDEX(Ranks, ',', 1) AS num,
        1 AS pos
    FROM your_table
    UNION ALL
    SELECT 
        id,
        SUBSTRING(remaining, LENGTH(num) + 2),
        SUBSTRING_INDEX(SUBSTRING(remaining, LENGTH(num) + 2), ',', 1),
        pos + 1
    FROM split_ranks
    WHERE remaining != ''
)
SELECT 
    t.id,
    t.Ranks,
    MIN(CAST(s.num AS UNSIGNED)) AS min_rank
FROM your_table t
JOIN split_ranks s ON t.id = s.id
GROUP BY t.id, t.Ranks;

如果表没有主键，可用ROW_NUMBER()生成临时标识：

WITH numbered_table AS (
    SELECT *, ROW_NUMBER() OVER () AS row_id FROM your_table
),
split_ranks AS (
    SELECT 
        row_id,
        Ranks AS remaining,
        SUBSTRING_INDEX(Ranks, ',', 1) AS num,
        1 AS pos
    FROM numbered_table
    UNION ALL
    SELECT 
        row_id,
        SUBSTRING(remaining, LENGTH(num) + 2),
        SUBSTRING_INDEX(SUBSTRING(remaining, LENGTH(num) + 2), ',', 1),
        pos + 1
    FROM split_ranks
    WHERE remaining != ''
)
SELECT 
    nt.Ranks,
    MIN(CAST(s.num AS UNSIGNED)) AS min_rank
FROM numbered_table nt
JOIN split_ranks s ON nt.row_id = s.row_id
GROUP BY nt.row_id, nt.Ranks;

PostgreSQL 方案 #

PostgreSQL支持数组操作，实现更简洁：

SELECT 
    Ranks,
    (SELECT MIN(unnest_val) FROM unnest(string_to_array(Ranks, ',')) AS unnest_val) AS min_rank
FROM your_table;

PostgreSQL 12+可直接用array_min函数：

SELECT 
    Ranks,
    array_min(string_to_array(Ranks, ',')::INT[]) AS min_rank
FROM your_table;

SQL Server 2016+ 方案 #

使用STRING_SPLIT函数拆分后分组求最小值：

SELECT 
    t.Ranks,
    MIN(CAST(s.value AS INT)) AS min_rank
FROM your_table t
CROSS APPLY STRING_SPLIT(t.Ranks, ',') s
GROUP BY t.Ranks;

需保留其他字段时，用主键关联：

SELECT 
    t.id,
    t.Ranks,
    MIN(CAST(s.value AS INT)) AS min_rank
FROM your_table t
CROSS APPLY STRING_SPLIT(t.Ranks, ',') s
GROUP BY t.id, t.Ranks;

老版本数据库自定义函数方案 #

如果数据库版本不支持上述内置函数，可创建自定义函数计算单条字符串的最小值：

MySQL 5.x 示例 #

DELIMITER //
CREATE FUNCTION get_min_rank(rank_str VARCHAR(255)) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE min_val INT;
    DECLARE remaining_str VARCHAR(255);
    DECLARE current_num INT;
    
    SET remaining_str = rank_str;
    SET min_val = CAST(SUBSTRING_INDEX(remaining_str, ',', 1) AS UNSIGNED);
    SET remaining_str = SUBSTRING(remaining_str, LENGTH(SUBSTRING_INDEX(remaining_str, ',', 1)) + 2);
    
    WHILE remaining_str != '' DO
        SET current_num = CAST(SUBSTRING_INDEX(remaining_str, ',', 1) AS UNSIGNED);
        IF current_num < min_val THEN
            SET min_val = current_num;
        END IF;
        SET remaining_str = SUBSTRING(remaining_str, LENGTH(SUBSTRING_INDEX(remaining_str, ',', 1)) + 2);
    END WHILE;
    
    RETURN min_val;
END //
DELIMITER ;

-- 调用函数
SELECT Ranks, get_min_rank(Ranks) AS min_rank FROM your_table;

注意事项 #

• 确保Ranks列的所有子串都是合法数值，否则需添加异常处理（比如跳过非数字值）
• 12000条记录的量级，上述方案性能均可满足需求

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