sql:如何使用sql查找字符串中给定字符连续序列的最大长度

SELECT MAX(LENGTH(sequence)) AS max_length
FROM (
  SELECT sequence
  FROM your_table
  WHERE sequence REGEXP 'a+'
) AS subquery;

SELECT MAX(length) AS max_length
FROM (
  SELECT sequence, length
  FROM (
    SELECT sequence,
           CASE WHEN @prev_char = character THEN @length := @length + 1 ELSE @length := 1 END AS length,
           @prev_char := character
    FROM (
      SELECT sequence, SUBSTRING(sequence, number, 1) AS character
      FROM your_table
      JOIN (
        SELECT 1 + a.number + b.number * 10 + c.number * 100 AS number
        FROM (SELECT 0 AS number UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS number UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS number UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS c
      ) AS numbers
      WHERE number <= LENGTH(sequence)
      ORDER BY sequence, number
    ) AS subquery,
    (SELECT @prev_char := '', @length := 0) AS vars
  ) AS subquery
  WHERE length > 1
) AS subquery;