如何在MySQL中合并多行? [英] How to merge multiple rows in MySQL?

问题描述

在构建数据库时遇到一些问题，我会问两个问题.

I ran into some problems while structuring my database, and I will ask two questions.

第一个问题:下表需要使用相同的ID合并

First question: below table needs to be merged by the same IDs

 ╔═════╦═══════╦═════╦═══════╗
 ║ id* ║ name  ║ age ║ grade ║
 ╠═════╬═══════╬═════╬═══════╣
 ║  0  ║ John  ║     ║       ║
 ║  0  ║       ║ 11  ║       ║
 ║  0  ║       ║     ║   6   ║
 ║  1  ║ Dave  ║     ║       ║
 ║  1  ║       ║ 12  ║       ║
 ║  1  ║       ║     ║   7   ║
 ╚═════╩═══════╩═════╩═══════╝

所以它应该看起来像这样；

so it should look like this;

 ╔═════╦═══════╦═════╦═══════╗
 ║ id* ║ name  ║ age ║ grade ║
 ╠═════╬═══════╬═════╬═══════╣
 ║  0  ║ John  ║ 11  ║   6   ║
 ║  0  ║ Dave  ║ 12  ║   7   ║
 ╚═════╩═══════╩═════╩═══════╝

注意:id *不是AUTO_INCREMENT

NOTE: id* is not AUTO_INCREMENT

第二个问题:您可能认为以前的数据库结构很差.好消息是，我还没有创建数据库，我一直在寻找一种在不删除旧信息的情况下将数据添加到现有行的解决方案，但是如果没有旧信​​息，它将创建一个新行.预先感谢.

Second question: You probably think that the former database structure is poor. The good thing is, I haven't created the database yet and I have been looking for a solution to add data to an existing row without removing old information, but if there is no old information, it would create a new row. Thanks in advance.

处女表

 ╔═════╦═══════╦═════╦═══════╗
 ║ id* ║ name  ║ age ║ grade ║
 ╠═════╬═══════╬═════╬═══════╣
 ║     ║       ║     ║       ║
 ╚═════╩═══════╩═════╩═══════╝

一些SQL语句

 ╔═════╦═══════╦═════╦═══════╗
 ║ id* ║ name  ║ age ║ grade ║
 ╠═════╬═══════╬═════╬═══════╣
 ║  0  ║ John  ║     ║       ║
 ╚═════╩═══════╩═════╩═══════╝

具有不同参数的相同SQL语句

 ╔═════╦═══════╦═════╦═══════╗
 ║ id* ║ name  ║ age ║ grade ║
 ╠═════╬═══════╬═════╬═══════╣
 ║  0  ║ John  ║     ║       ║
 ║  1  ║ Dave  ║     ║       ║
 ╚═════╩═══════╩═════╩═══════╝

另一个SQL语句

 ╔═════╦═══════╦═════╦═══════╗
 ║ id* ║ name  ║ age ║ grade ║
 ╠═════╬═══════╬═════╬═══════╣
 ║  0  ║ John  ║     ║       ║
 ║  1  ║ Dave  ║ 12  ║       ║
 ╚═════╩═══════╩═════╩═══════╝

另一个SQL语句

 ╔═════╦═══════╦═════╦═══════╗
 ║ id* ║ name  ║ age ║ grade ║
 ╠═════╬═══════╬═════╬═══════╣
 ║  0  ║ John  ║     ║   6   ║
 ║  1  ║ Dave  ║ 12  ║       ║
 ╚═════╩═══════╩═════╩═══════╝

...等等.

推荐答案

您应该能够将聚合函数应用于所有列，然后应用于GROUP BY id:

You should be able to apply an aggregate function to all the columns and then GROUP BY id:

select id,
  max(name) name,
  max(age) age,
  max(grade) grade
from yourtable
group by id

请参见带有演示的SQL提琴

就数据库结构而言，我看到的唯一问题是您要为同一用户插入多个记录.您应该使用UPDATE语句来使用值，而不要插入.

As far as the DB structure, the only issue that I see is that you are inserting multiple records for the same user. You should be using an UPDATE statement to use the values instead of inserting.

听起来您想使用 REPLACE MySQL中的函数(这是一个教程).

因此查询与此类似:

REPLACE 
  INTO yourtable (`id`, `name`, `age`, `grade`) 
  VALUES (0, 'john', 11, null);

请参见带有演示的SQL小提琴

这篇关于如何在MySQL中合并多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！