The MySQL database operation of the interview written test frequently group by

In the IT interview, the related problems of database are basically the required questions, and the SQL statement is also an important point of knowledge frequently inspected.

The following describes a more important operation in the SQL statement group by, his important line on the one hand is reflected in his understanding of the difficulty, on the one hand embodies the application of long-seeing.

First, give a studnet student table:

CREATE TABLE ' student ' (  ' id ' int (one) not null auto_increment,  ' name ' varchar () DEFAULT NULL,  ' sex ' tinyint (1) Default ' 0 ',  ' score ' int (ten) not NULL,  ' dept ' varchar (TEN) default NULL,  

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 |+----+------+------+-------+---------+

Give the demand, write the SQL:

Give the highest student scores in each department.

To get to the students in each department, the first thing is to group them, group them according to the department, and then find the highest scores in each department.

So 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     |   | |  3 | b    |    0 |    88 | Design  |   | |  4 | C    |    0 |    60 | Sales   |   | |  6 | D    |    1 |   100 | Product |  |+----+------+------+-------+---------+------+4 rows in Set (0.00 sec)

This is just a simple example, we can complicate this example, such as the highest score must be a female, that is, the sex column value must be 1 to pick out, then the SQL statement should be:

Mysql> Select *,max (Score) as Max from student Group by dept have sex= ' 1 ' ORDER by name;+----+------+------+-------+ + --------+------+| ID | name | Sex  | score | Dept    | Max  |+----+------+------+-------+---------+------+|  1 | A    |    1 |    90 | Dev     |   | |  6 | D    |    1 |   100 | Product |  |+----+------+------+-------+---------+------+2 rows in Set (0.46 sec)

Here we do not use the WHERE statement but with having, here is a simple explanation, because our condition is in the group after the sex= ' 1 ', and then in accordance with the Dept division, is also feasible, here to see the topic is how to ask:

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     |   | |  6 | D    |    1 |   100 | Product |  |+----+------+------+-------+---------+------+2 rows in Set (0.05 sec)

The results of the query are consistent, if the selection criteria must be changed to the department owner of the score of more than 150 to the highest scores of the Department of the people listed, here must use having, because having inside can use aggregate function sum, And we have to split the group so we can get the total score of this group before we can compare the value to 150:

Mysql> Select *,max (Score) as Max from Student   Group BY dept have sum (score) >150 ORDER by name;+----+------+-- ----+-------+---------+------+| ID | name | Sex  | score | Dept    | Max  |+----+------+------+-------+---------+------+|  1 | A    |    1 |    90 | Dev     |   | |  6 | D    |    1 |   100 | Product |  |+----+------+------+-------+---------+------+2 rows in Set (0.00 sec)

Add an additional example, for example, if I want to select a non-repeating department, we can use

Mysql> SELECT DISTINCT dept from student;+---------+| Dept    |+---------+| Dev     | | design  | | Sales   | | product |+---------+4 rows in Set (0.02 sec)

But if we also want to list some other information such as his ID, if we do:

Mysql> Select Name,distinct Dept from student; Error 1064 (42000): You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use-near ' distinct-dept from Studen T ' at line 1

This is not possible, because the distinct can only be placed in 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 did not achieve the desired effect, because distinct effect to 2 fields, then we need to Groub by the appearance.

Mysql> Select  dept,name from student GROUP by dept;+---------+------+| Dept    | name |+---------+------+| Design  | b    | | Dev     | a    | | product | d    | | sales   | c    |+---------+------+4 rows in Set (0.0 0 sec)

According to the Dept group, Nature has reached the goal of weight. So sometimes if we come across a problem that is hard to solve, like using distinct to weigh and bring in other column values, we need to try a different idea, and perhaps the answer will be found naturally.