The mysql database operation groupby that is frequently used in the interview test.

During the IT interview, database-related questions are basically mandatory, and SQL statements are also an important knowledge point that is frequently investigated. Next we will introduce groupby, an important operation in SQL statements. Its important lines are reflected in the difficulty of understanding and the extensiveness of applications. First, a studnet student table: [s

During the IT interview, database-related questions are basically mandatory, and SQL statements are also an important knowledge point that is frequently investigated. Next we will introduce an important operation group by in an SQL statement. Its important line is reflected in the difficulty of understanding and the extensiveness of the application. First, a studnet student table: [s

During the IT interview, database-related questions are basically mandatory, and SQL statements are also an important knowledge point that is frequently investigated.

Next we will introduce an important operation group by in an SQL statement. Its important line is reflected in the difficulty of understanding and the extensiveness of the application.

First, a studnet student table is provided:

[SQL]View plaincopyprint?

1. Create table 'student '(
2. 'Id' int (11) not null AUTO_INCREMENT,
3. 'Name' varchar (30) default null,
4. 'Sex' tinyint (1) DEFAULT '0 ',
5. 'Score 'int (10) not null,
6. 'Dept' varchar (10) default null,
7. Primary key ('id ')
8. ) ENGINE = MyISAM AUTO_INCREMENT = 8 default charset = utf8

CREATE TABLE `student` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(30) DEFAULT NULL,  `sex` tinyint(1) DEFAULT '0',  `score` int(10) NOT NULL,  `dept` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 

Add some test data:

[SQL]View plaincopyprint?

1. Mysql> select * from student where id <10;
2. + ---- + ------ + ------- + --------- +
3. | Id | name | sex | score | dept |
4. + ---- + ------ + ------- + --------- +
5. | 1 | a | 1 | 90 | dev |
6. | 2 | B | 1 | 90 | dev |
7. | 3 | B | 0 | 88 | design |
8. | 4 | c | 0 | 60 | sales |
9. | 5 | c | 0 | 89 | sales |
10. | 6 | d | 1 | 1 | 100 | product |
11. + ---- + ------ + ------- + --------- +

mysql> select * from student where id<10;+----+------+------+-------+---------+| id | name | sex  | score | dept    |+----+------+------+-------+---------+|  1 | a    |    1 |    90 | dev     ||  2 | b    |    1 |    90 | dev     ||  3 | b    |    0 |    88 | design  ||  4 | c    |    0 |    60 | sales   ||  5 | c    |    0 |    89 | sales   ||  6 | d    |    1 |   100 | product |+----+------+------+-------+---------+

Provide the requirement and write the SQL statement:

The highest score of students in each department.

To get students from different departments, you must first group them by department, and then find the highest score in each department.

Therefore, the SQL statement is:

[SQL]View plaincopyprint?

1. Mysql> select *, max (score) as max from student group by dept order by name;
2. + ---- + ------ + ------- + --------- + ------ +
3. | Id | name | sex | score | dept | max |
4. + ---- + ------ + ------- + --------- + ------ +
5. | 1 | a | 1 | 90 | dev | 90 |
6. | 3 | B | 0 | 88 | design | 88 |
7. | 4 | c | 0 | 60 | sales | 89 |
8. | 6 | d | 1 | 100 | product | 100 |
9. + ---- + ------ + ------- + --------- + ------ +
10. 4 rows in set (0.00 sec)

mysql> select *, max(score) as max  from student group by dept order by name;+----+------+------+-------+---------+------+| id | name | sex  | score | dept    | max  |+----+------+------+-------+---------+------+|  1 | a    |    1 |    90 | dev     |   90 ||  3 | b    |    0 |    88 | design  |   88 ||  4 | c    |    0 |    60 | sales   |   89 ||  6 | d    |    1 |   100 | product |  100 |+----+------+------+-------+---------+------+4 rows in set (0.00 sec)

This is just a simple example. We can complicate this example. For example, the highest score must be girl, that is, the sex column value must be 1, the SQL statement should be:

[SQL]View plaincopyprint?

1. Mysql> select *, max (score) as max from student group by dept having sex = '1' order by name;
2. + ---- + ------ + ------- + --------- + ------ +
3. | Id | name | sex | score | dept | max |
4. + ---- + ------ + ------- + --------- + ------ +
5. | 1 | a | 1 | 90 | dev | 90 |
6. | 6 | d | 1 | 100 | product | 100 |
7. + ---- + ------ + ------- + --------- + ------ +
8. 2 rows in set (0.46 sec)

mysql> select *,max(score) as max from student group by dept having sex='1' order by name;+----+------+------+-------+---------+------+| id | name | sex  | score | dept    | max  |+----+------+------+-------+---------+------+|  1 | a    |    1 |    90 | dev     |   90 ||  6 | d    |    1 |   100 | product |  100 |+----+------+------+-------+---------+------+2 rows in set (0.46 sec)

Having is not used here, but is used here for simple explanation, because our condition is After grouping. In fact, sex = '1' is selected before grouping ', it is also feasible to group by dept department. Here we will look at the Requirements of the subject:

[SQL]View plaincopyprint?

1. Mysql> select *, max (score) as max from student where sex = '1' group by dept order by name;
2. + ---- + ------ + ------- + --------- + ------ +
3. | Id | name | sex | score | dept | max |
4. + ---- + ------ + ------- + --------- + ------ +
5. | 1 | a | 1 | 90 | dev | 90 |
6. | 6 | d | 1 | 100 | product | 100 |
7. + ---- + ------ + ------- + --------- + ------ +
8. 2 rows in set (0.05 sec)

mysql> select *,max(score) as max from student where sex='1' group by dept order by name;+----+------+------+-------+---------+------+| id | name | sex  | score | dept    | max  |+----+------+------+-------+---------+------+|  1 | a    |    1 |    90 | dev     |   90 ||  6 | d    |    1 |   100 | product |  100 |+----+------+------+-------+---------+------+2 rows in set (0.05 sec)

The query results are consistent. If you change the selection condition to a condition where the total score of all Department members is greater than 150 to list the persons in the department with the highest score, having must be used here, because the aggregate function sum can be used in having, and the total score of this group can be obtained only after dividing the group to compare whether the value is greater than 150:

[SQL]View plaincopyprint?

1. Mysql> select *, max (score) as max from student group by dept having sum (score)> 150 order by name;
2. + ---- + ------ + ------- + --------- + ------ +
3. | Id | name | sex | score | dept | max |
4. + ---- + ------ + ------- + --------- + ------ +
5. | 1 | a | 1 | 90 | dev | 90 |
6. | 6 | d | 1 | 100 | product | 100 |
7. + ---- + ------ + ------- + --------- + ------ +
8. 2 rows in set (0.00 sec)

mysql> select *,max(score) as max from student   group by dept having sum(score)>150 order by name;+----+------+------+-------+---------+------+| id | name | sex  | score | dept    | max  |+----+------+------+-------+---------+------+|  1 | a    |    1 |    90 | dev     |   90 ||  6 | d    |    1 |   100 | product |  100 |+----+------+------+-------+---------+------+2 rows in set (0.00 sec)

Add an example. For example, if you want to select a department that is not repeated, you can use

[SQL]View plaincopyprint?

1. Mysql> select distinct dept from student;
2. + --------- +
3. | Dept |
4. + --------- +
5. | Dev |
6. | Design |
7. | Sales |
8. | Product |
9. + --------- +
10. 4 rows in set (0.02 sec)

mysql> select distinct dept from student;+---------+| dept    |+---------+| dev     || design  || sales   || product |+---------+4 rows in set (0.02 sec)

However, if we want to list other information such as his id, we should:

[SQL]View plaincopyprint?

1. Mysql> select name, distinct dept from student;
2. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct dept from student 'at line 1

mysql> select name,distinct dept from student;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct dept from student' at line 1

This is not acceptable, because distinct can only be placed at the starting position, if:

[SQL]View plaincopyprint?

1. Mysql> select distinct dept, name from student;
2. + --------- + ------ +
3. | Dept | name |
4. + --------- + ------ +
5. | Dev | a |
6. | Dev | B |
7. | Design | B |
8. | Sales | c |
9. | Product | d |
10. | Product | m |
11. + --------- + ------ +
12. 6 rows in set (0.00 sec)

mysql> select distinct  dept,name from student;+---------+------+| dept    | name |+---------+------+| dev     | a    || dev     | b    || design  | b    || sales   | c    || product | d    || product | m    |+---------+------+6 rows in set (0.00 sec)

Why didn't we achieve the expected results? Because distinct applies to two fields, then we need to start groub.

[SQL]View plaincopyprint?

1. Mysql> select dept, name from student group by dept;
2. + --------- + ------ +
3. | Dept | name |
4. + --------- + ------ +
5. | Design | B |
6. | Dev | a |
7. | Product | d |
8. | Sales | c |
9. + --------- + ------ +
10. 4 rows in set (0.00 sec)

mysql> select  dept,name from student group by dept;+---------+------+| dept    | name |+---------+------+| design  | b    || dev     | a    || product | d    || sales   | c    |+---------+------+4 rows in set (0.00 sec)

Grouping by dept will naturally achieve de-duplication. So sometimes if we encounter a problem that is difficult to solve, such as removing duplicates with distinct and adding other column values, we need to change our thinking, and the answer will naturally be found.