MySQL - 查询组合具有相同 id 的行并保留该 id 的所有条目但作为一条记录

2022-01-05 00:00:00 sql mysql phpmyadmin wampserver

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

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

我想要的是合并具有相同植物 ID 的记录并在不同的列中显示不同的名称:

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;

这似乎在没有错误的情况下工作，但它没有合并记录，它只保留了第一条记录的名称和 id 而不是其余记录.使用的数据:

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

并将其用作子查询.

相关文章