MySQL-查询将具有相同ID的行合并，并保留该ID的所有条目但作为一条记录 [英] MySQL - Query combine rows with same id and keep all entry for that id but as one record

问题描述

我一直在处理mysql服务器中本地存储在wamp服务器上的表，而我正在phpmyadmin区域中使用in wamp运行查询.我正在尝试获取数据来执行以下操作:

I have been working on a table in a mysql database being hold locally on a wamp server I am using the phpmyadmin area with in wamp to run the querys. I am trying to get the data to do the following:

任何人都可以帮我建立一个带有许多植物记录的表格.植物可以具有多个名称，该表将其显示为不同的记录.该表称为new_plantsname

Can anyone help I have a table with a number of records for plants. A plant can have a number of names the table shows this as different records. The table is called new_plantsname

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

这将继续进行超过3000条记录

this continues for over 3000 records

我想要的是将具有相同Plantid的记录合并在一起，并在不同的列中显示不同的名称:

what i want is it to combined records with same plantid and show the different names in different columns:

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

等

乍一看，我相信一种植物的名称不超过4个.

From a glance I believe a plant has no more than 4 names.

可以帮助我查询此操作吗?我也想将结果保存到新表中

Can one help me the query to do this. I also want to save results to a new table

有人给了我以下答案:

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:=@rn+1 overall_row_num
    from yourtable, (SELECT @rn:=0) r
  ) x
  order by plantid, overall_row_num
) src
group by plantid;

这似乎可以正常工作，但没有错误，但是它没有合并记录，只保留了第一个记录的名称，而没有保留其他记录. 使用的数据:

This seem to work while no errors but it didnt combine the records it only kept the name of the first record with the id not the rest. DATA USED:

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

结果:

任何人都可以帮助

推荐答案

问题是MySQL没有枚举行的好方法.根据MySQL文档，常量的使用不能保证正常工作.它通常可以工作，但也可能会出现问题.

The problem is that MySQL does not have a good way of enumerating rows. The use of the constant is not guaranteed to work, alas, according to the MySQL documentation. It often does work, but it can also be problematic.

我建议您将名称连接到一个字段中.结果如下:

I would suggest that you concatenate the names together into a single field. The result would look like:

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

使用SQL:

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

如果您真的想要在单独的栏中输入名称，则可以想到两个选项.一种是使用上面的结果，然后将结果解析为单独的字符串.另一种选择是使用自联接和聚合来计算序列号，如下所示:

If you really wanted the names in separate columns, two options come to mind. One is to use the results from above and then parse the result into separate strings. The other alternative is to use a self-join and aggregation to calculate a sequential number, like this:

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

并将其用作子查询.

这篇关于MySQL-查询将具有相同ID的行合并，并保留该ID的所有条目但作为一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！