Группируйте последовательно значения в MySQL и добавляйте id в такие группы

У меня есть простая таблица, и мне нужно идентифицировать группы из четырех строк (группы не являются последовательными), но каждая строка каждой строки имеет +1 в значении. Например:

----------------------
| язык | id  |
----------------------
| C            |  16 |
| C++          |  17 |
| Java         |  18 |
| Python       |  19 |
| HTML         |  65 |
| JavaScript   |  66 |
| PHP          |  67 |
| Perl         |  68 |
----------------------

Я хочу добавить столбец, который указывает группу или набор, как можно получить этот вывод, используя MySQL?:

----------------------------
| язык | id | установить |
----------------------------
| C            |  16 |  1  |
| C++          |  17 |  1  |
| Java         |  18 |  1  |
| Python       |  19 |  1  |
| HTML         |  65 |  2  |
| JavaScript   |  66 |  2  |
| PHP          |  67 |  2  |
| Perl         |  68 |  2  |
----------------------------

Обратите внимание, что в этом примере только 2 набора (это может быть 1 или более наборов), и они не начинаются с 16 (такие значения не известны, но ограничение состоит в том, что каждое значение id каждой строки имеет эту форму n, n+1, n+2 и n+3).

Я изучал проблему Gaps & Islands, но не понял, как ее решить, используя их решения. Также я ищу на stackru, но самый близкий вопрос, который я нашел, был Как найти пробелы в последовательной нумерации в MySQL?

Спасибо

Источник

3 ответа

Решение
select language, 
       @n:=if(@m+1=id, @n, @n+1) `set`, 
       (@m:=id) id 
   from t1, 
        (select @n:=0) n, 
        (select @m:=0) m

Демо на sqlfiddle

Источник 
SELECT language,id,g
FROM (
  SELECT language,id,
    CASE WHEN id=@lastid+1 THEN @n ELSE @n:=@n+1 END AS g,
    @lastid := id As b
  FROM
    t, (SELECT @n:=0) r
  ORDER BY
    id
) s

РЕДАКТИРОВАТЬ

Если вам нужно только 4 на группу, добавьте переменную номера строки:

SELECT language,id,g,rn
FROM (
  SELECT language,id,
    CASE WHEN id=@lastid+1 THEN @n ELSE @n:=@n+1 END AS g,
   @rn := IF(@lastid+1 = id, @rn + 1, 1) AS rn,
    @lastid := id As dt
  FROM
    t, (SELECT @n:=0) r
  ORDER BY
    id
) s
Where rn <=4

FIDDLE

Источник

Вы можете использовать следующий запрос:

SELECT l.*, s.rn
FROM languages AS l
INNER JOIN (
  SELECT minID, @rn2:=@rn2+1 AS rn
  FROM (
    SELECT MIN(id) AS minID
    FROM (
      SELECT id,
             id - IF (true, @rn1:=@rn1+1, 0) AS grp
      FROM languages
      CROSS JOIN (SELECT @rn1:=0) AS var1 
      ORDER BY id) t    
    GROUP BY grp
    HAVING COUNT(grp) = 4 ) u
  CROSS JOIN (SELECT @rn2:=0) AS var2  
) s ON l.id BETWEEN minID AND minID + 3

Приведенный выше запрос идентифицирует острова ровно из 4 последовательных записей и возвращает только записи. Его легко изменить, чтобы учесть различное количество последовательных записей.

Обратите также внимание на использование IF условно: это гарантирует, что @rn1 сначала инициализируется, а затем используется для расчета grp поле.

Демо здесь

Источник
Другие вопросы по тегам