MySQL - запрос объединяет строки с одинаковым идентификатором и сохраняет все записи для этого идентификатора, но как одну запись

Я работаю над таблицей в базе данных mysql, находящейся локально на сервере wamp. Я использую область phpmyadmin с помощью wamp для запуска запросов. Я пытаюсь заставить данные сделать следующее:

Может кто-нибудь помочь? У меня есть таблица с несколькими записями для растений. Завод может иметь несколько имен, таблица показывает это как разные записи. Таблица называется new_plantsname

plantid name
1       tree
1       rose
2       bush
3       tree
3       bush
3       rose

это продолжается для более 3000 записей

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

plantid name1 name2 name3 ...
1       tree  rose  NULL
2       shrub NULL  NULL
3       tree  rose  bush

и т.д.

С первого взгляда я считаю, что завод имеет не более 4 имен.

Можно ли мне помочь в этом вопросе. Я также хочу сохранить результаты в новой таблице

Кто-то дал мне следующее для ответа:

select plantid,
  max(case when nameRn = 'name1' then name end) Name1,
  max(case when nameRn = 'name2' then name end) Name2,
  max(case when nameRn = 'name3' then name end) Name3,
  max(case when nameRn = 'name4' then name end) Name4
from
(
  select plantid, name,
      concat('name', @num := if(@plantid = `plantid`, @num + 1, 1)) as nameRn,
      @plantid := `plantid` as dummy
  from
  (
    select plantid, name, @rn:[email protected]+1 overall_row_num
    from yourtable, (SELECT @rn:=0) r
  ) x
  order by plantid, overall_row_num
) src
group by plantid;

Кажется, что это работает, пока нет ошибок, но он не сочетал записи, в которых он сохранял только имя первой записи с идентификатором, а не остальным. ИСПОЛЬЗУЕМЫЕ ДАННЫЕ:

plantid     name
    1       tree
    1       rose
    2       tree
    3       rose
    3       bush
    3       rose

Результаты:

Может ли кто-нибудь помочь

Ответ 1

Проблема заключается в том, что MySQL не имеет хорошего способа перечисления строк. Использование константы не гарантирует работу, увы, согласно документации MySQL. Он часто работает, но он также может быть проблематичным.

Я предлагаю вам объединить имена вместе в одно поле. Результат будет выглядеть так:

1     tree,rose
2     tree
3     tree,bush,rose

Использование SQL:

select plantid, group_concat(name separator ',')
from t
group by plantid

Если вам действительно нужны имена в отдельных столбцах, вам приходят в голову два варианта. Один из них - использовать результаты сверху, а затем анализировать результат на отдельные строки. Другой альтернативой является использование самосоединения и агрегации для вычисления последовательного числа, например:

select p.plantid, p.name, count(*) as seqnum
from t p left outer join
     t pprev
     on p.plantid = pprev.plantid and
        p.name >= pprev.name
group by p.plantid, p.name

И используйте это как подзапрос.