如何在sql中将行与同一列合并？

Table1: Factor_Name

ID NAME
1  A
2  A
3  B

Table2: Sample_Info

   ID   FACTOR_ID   INFO
   1    1           Sample_A
   2    2           Sample_B
   3    3           Sample_C

I want to merge the column in the 2 tables if the factor names are same in table1, this is the expected table:

如果表1中的因子名称相同,我想合并2个表中的列,这是预期的表:

Table1: Factor_Name

ID NAME
1  A
2  B

Table2: Sample_Info

   ID   FACTOR_ID   INFO
   1    1           Sample_A
   2    1           Sample_B
   3    2           Sample_C

How can I do that in mysql?

我怎么能在mysql中做到这一点?

1 个解决方案

#1

You're going to want to do this by first duplicating the tables you currently have:

您将要通过首先复制当前的表来实现此目的:

CREATE TEMPORARY TABLE Factor_Name_Temp as (SELECT * 
                                            FROM Factor_Name) 

CREATE TEMPORARY TABLE Sample_Info_Temp as (SELECT * 
                                            FROM Sample_Info)

Then, you're going to need to clear the contents of the existing tables. You also appear to want to reset the id columns. This could be slightly dangerous, depending on how many other tables reference these values. But either way...

然后,您将需要清除现有表的内容。您似乎也想重置id列。这可能有点危险,具体取决于有多少其他表引用这些值。但是无论哪种方式...

Then, insert the data from the 'temp' tables back into the original tables. Leave off the id column if you're having them re-generated, otherwise this will choose the initial 'minimum' value (ie, there'll be gaps).

然后,将'temp'表中的数据插回到原始表中。如果你重新生成它们,请不要使用id列,否则这将选择初始的“最小”值(即,会有间隙)。

INSERT Factor_Name (id, name) 
SELECT MIN(id), name
FROM Factor_Name_Temp
GROUP BY name

INSERT Sample_Info (id, factor_id, info)
SELECT Sample_Info_Temp.id, Factor_Name.id, Sample_Info_Temp.info
FROM Sample_Info_Temp
JOIN Factor_Name_Temp
  ON Factor_Name_Temp.id = Sample_Info_Temp.factor_id
JOIN Factor_Name
  ON Factor_Name.name = Factor_Name_Temp.name

Don't forget to clean up after yourself!

不要忘记自己清理!

(Statements untested - I lack a mySQL instance, and SQL Fiddle isn't completely conductive to this).

(声明未经测试 - 我缺少一个mySQL实例,而SQL Fiddle对此并不完全具有导电性)。

#1