mysql怎么合并行,如何在MySQL中合并多行？

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

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 ║

╚═════╩═══════╩═════╩═══════╝

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.

Second question explained

Virgin table

╔═════╦═══════╦═════╦═══════╗

║ id* ║ name ║ age ║ grade ║

╠═════╬═══════╬═════╬═══════╣

║ ║ ║ ║ ║

╚═════╩═══════╩═════╩═══════╝

some SQL statement

╔═════╦═══════╦═════╦═══════╗

║ id* ║ name ║ age ║ grade ║

╠═════╬═══════╬═════╬═══════╣

║ 0 ║ John ║ ║ ║

╚═════╩═══════╩═════╩═══════╝

the same SQL statement with different parameters

╔═════╦═══════╦═════╦═══════╗

║ id* ║ name ║ age ║ grade ║

╠═════╬═══════╬═════╬═══════╣

║ 0 ║ John ║ ║ ║

║ 1 ║ Dave ║ ║ ║

╚═════╩═══════╩═════╩═══════╝

another SQL statement

╔═════╦═══════╦═════╦═══════╗

║ id* ║ name ║ age ║ grade ║

╠═════╬═══════╬═════╬═══════╣

║ 0 ║ John ║ ║ ║

║ 1 ║ Dave ║ 12 ║ ║

╚═════╩═══════╩═════╩═══════╝

another SQL statement

╔═════╦═══════╦═════╦═══════╗

║ id* ║ name ║ age ║ grade ║

╠═════╬═══════╬═════╬═══════╣

║ 0 ║ John ║ ║ 6 ║

║ 1 ║ Dave ║ 12 ║ ║

╚═════╩═══════╩═════╩═══════╝

... and so on.

解决方案

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

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.

It sounds like you want to use the REPLACE function in MySQL (here is a tutorial).

So the query would be similar to this:

REPLACE

INTO yourtable (`id`, `name`, `age`, `grade`)

VALUES (0, 'john', 11, null);