Mysql group by usage details, mysqlgroupby details

Mysql group by usage details, mysqlgroupby details

Background

Recently, when designing a database, a large amount of duplicate data has been generated due to the lack of weeks. Now you need to delete the duplicate data. The statement used is Group. To further understand the role of this statement, I plan to start with a simple statement.

Create a test table

Copy codeThe Code is as follows: create table test_group (id int auto_increment primary key, name varchar (32), class varchar (32), score int );

View table structure

Desc test_group

Insert data

Test started

I want to know who is the highest score in each class.

Copy codeThe Code is as follows: select name, class, max (score) from test_group group by class;

Now you can insert several duplicate data records.

Copy codeThe Code is as follows: insert into test_group (name, class, score) values ('repeat', 'B', 89 );

Now we need to filter out the duplicate data and keep the latest record. Generally, we assume that the latest record is the last inserted record, so its ID should be the largest one.

Copy codeThe Code is as follows: select name, class, max (id) from test_group group by name;

We can find that we put the item behind gourp by focusing on its repeatability. In this way, we can filter out records that are repeated with this item. Now we get the data we need. The next step is to delete the duplicate data. To distinguish the data records we have filtered from the original records, we can give the id an alias.

Copy codeThe Code is as follows: select name, class, max (id) as max_id from test_group group by name;

The next step is to keep the data of interest.idExtracted. This is the only one that determines a record.

Copy codeThe Code is as follows: select max_id from (select name, class, max (id) as max_id from test_group group by name) B;

The following is the delete operation. The idea is to delete records whose data IDs are not in our query results. To facilitate data comparison after operations, I first perform a full query.

Copy codeThe Code is as follows: select * from test_group;

Perform the delete operation.

Copy codeThe Code is as follows: delete from test_group where id not in (select max_id from (select name, class, max (id) as max_id from test_group group by name) B );

Finally, view the result.

Summary

MySQL operations are quite flexible. I have always liked Using ORM. Now I feel that using MYSQL directly saves a lot of trouble. If you have a better and more efficient way, please share it with me ~~

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.