Mysql database operations groupby
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:
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:
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:
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:
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:
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:
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
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:
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:
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.
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.