实现“mysql 逗号分隔取最大值”的方法

步骤流程

flowchart TD
    A(输入逗号分隔的数据) --> B(将数据按逗号分隔成数组)
    B --> C(取数组中的最大值)
    C --> D(输出最大值)

详细步骤

  1. 将逗号分隔的数据按逗号分隔成数组
```sql
SET @data = '1,3,5,2,4'; -- 定义逗号分隔的数据
SET @max = 0; -- 初始化最大值为0

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@data, ',', numbers.n), ',', -1) AS value
FROM
(
    SELECT @row := @row + 1 AS n
    FROM
    (
        SELECT 0 AS dummy
        UNION ALL
        SELECT 1
        UNION ALL
        SELECT 2
        UNION ALL
        SELECT 3
        UNION ALL
        SELECT 4
    ) d1
    JOIN
    (
        SELECT 0 AS dummy
        UNION ALL
        SELECT 1
        UNION ALL
        SELECT 2
        UNION ALL
        SELECT 3
        UNION ALL
        SELECT 4
    ) d2
    JOIN
    (
        SELECT @row := 0
    ) numbers
) numbers
WHERE
    LENGTH(@data) - LENGTH(REPLACE(@data, ',', '')) >= n - 1;
  1. 取数组中的最大值
```sql
SET @data = '1,3,5,2,4'; -- 定义逗号分隔的数据
SET @max = 0; -- 初始化最大值为0

SELECT MAX(value) INTO @max
FROM
(
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@data, ',', numbers.n), ',', -1) AS value
    FROM
    (
        SELECT @row := @row + 1 AS n
        FROM
        (
            SELECT 0 AS dummy
            UNION ALL
            SELECT 1
            UNION ALL
            SELECT 2
            UNION ALL
            SELECT 3
            UNION ALL
            SELECT 4
        ) d1
        JOIN
        (
            SELECT 0 AS dummy
            UNION ALL
            SELECT 1
            UNION ALL
            SELECT 2
            UNION ALL
            SELECT 3
            UNION ALL
            SELECT 4
        ) d2
        JOIN
        (
            SELECT @row := 0
        ) numbers
    ) numbers
    WHERE
        LENGTH(@data) - LENGTH(REPLACE(@data, ',', '')) >= n - 1
) tmp;
  1. 输出最大值
```sql
SELECT @max AS max_value; -- 输出最大值

关系图

erDiagram
    数据表 {
        INT id
        VARCHAR data
    }

结尾

通过以上步骤，你可以实现在 MySQL 中逗号分隔的数据中取最大值的操作。希望这篇文章对你有所帮助，如果有任何疑问，欢迎随时向我提问。祝你学习进步！