将具有相同ID的行合并为一行,保留所有数据 [英] combined rows with same id in to one row keeping all data
问题描述
任何人都可以帮忙吗,我有一张桌子,上面有许多植物的记录.植物可以具有多个名称,该表将其显示为不同的记录.该表称为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
等
可以帮助我查询此操作吗?我也想将结果保存到新表中
Can one help me the query to do this. I also want to save results to a new table
预先感谢
推荐答案
这基本上是一个PIVOT
(您未指定RDBMS),我假设MySQL并不具有PIVOT
函数,因此您需要使用带有CASE
语句的聚合函数复制此代码.此解决方案将rownumber
添加到每一行,以便您确定需要转换为列的多少name
值.
This is basically a PIVOT
(you did not specify RDBMS) I am assuming MySQL and it does not have a PIVOT
function so you will need to replicate this using an aggregate function with a CASE
statement. This solution adds a rownumber
to each row so you can determine how many name
values you need to transform to columns.
如果您知道要拥有多少name
个值,则可以对这些值进行硬编码:
If you know how many name
values you are going to have you can hard-code the values:
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
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;
请参见带有演示的SQL提琴
如果值的数量未知,则可以使用准备好的语句来生成该值的动态版本:
If you have an unknown number of values, then you can use a prepared statement to generate a dynamic version of this:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when nameRn = ''',
nameRn,
''' then name end) AS ',
nameRn
)
) INTO @sql
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;
SET @sql = CONCAT('SELECT plantid, ', @sql, '
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');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
请参见带演示的SQL提琴
两者都会产生相同的结果:
Both will generate the same result:
| PLANTID | NAME1 | NAME2 | NAME3 |
-------------------------------------
| 1 | tree | rose | (null) |
| 2 | bush | (null) | (null) |
| 3 | tree | bush | rose |
这篇关于将具有相同ID的行合并为一行,保留所有数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!