Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "what is the use of group by in the database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Some time ago, I came across such an interview question during the interview, because I hadn't contacted sql for a long time and didn't write it out.

As shown in the picture, there is such a transcript:

First of all, we should understand the meaning of group by: "Group By" literally means to group data according to the rules specified by "By". The so-called grouping is to divide a "data set" into several "small areas", and then process the data for several "small areas".

Let's first take a look at this sql statement: select subject,max (score) from grade GROUP BY subject

The result is:

In this way, we correctly get the highest score of each subject and the name of the subject. Is it possible to add a name to the end to get the corresponding student's name? We can try: select subject,max (score), name from grade GROUP BY subject

When I looked at the results, I thought it was successful, but after comparing the data, I found that it was wrong, and the students' names and scores did not correspond to each other. If you think it's successful because you don't know enough about group by, I don't know why this query can produce results. My mysql database will report an error if it is oracle.

Note: because the fields specified in select are either included at the end of the Group By statement as the basis for grouping, or are included in the aggregate function.

So this is wrong.

The field specified by select in the group by statement must be "grouped by field". Other fields must be included in the aggregate function if they want to appear in the select. Common aggregate functions are as follows:

Function function support sum (column name) sum max (column name) maximum value min (column name) minimum value avg (column name) average first (column name) first record only Access supports last (column name) the last record only Access supports count (column name) statistical record number attention and count (*)

Let's analyze the requirements and write sql statements through the requirements.

Here are several ways to do this:

We have used group by grouping to get the highest score for each subject and the subject name, using it as the first sql, and then querying the score table to find records with the same discipline and score: (sub-sql statement as part of the main sql statement)

# a.* represents all fields in table a, and b.* represents all fields in table b

Select b.* from (select subject,max (score) m from grade GROUP BY subject) t grade b where t.subject=b.subject and t.m=b.score

The results are as follows:

The comparison shows that the data are correct.

Expand the question: use a SQL to find out all the students with the highest and lowest scores in the course and their scores.

First of all, the highest and lowest scores of each discipline are obtained by grouping:

Select subject,max (score), MIN (score) from grade GROUP BY subject

The results are as follows:

So how do we put the student name corresponding to the highest score and the name corresponding to the lowest score, and the required data display is the highest score line and the lowest score line. So it won't work.

The idea drawn from the first question above:

Select b.* from (select subject,max (score) m from grade GROUP BY subject) t grade b where t.subject=b.subject and t.m=b.score

In this way, since you can get the highest score of each discipline, the name of the student, the name of the discipline, then you can also change max (score) to min (score), then you can get the lowest score, the name of the student, the name of the subject? The focus now is on how to integrate the results of the two sql statements.

Select b.* from (select subject,min (score) m from grade GROUP BY subject) t grade b where t.subject=b.subject and t.m=b.score

At this point, I think of the keyword of sql: the definition of UNION

The UNION operator is used to merge the result sets of two or more SELECT statements.

Note that SELECT statements within UNION must have the same number of columns. Columns must also have similar data types. At the same time, the columns in each SELECT statement must be in the same order. Note: by default, the UNION operator selects a different value. If duplicate values are allowed, use UNION ALL. In addition, the column name in the UNION result set is always equal to the column name in the first SELECT statement in UNION.

So the resulting sql goes like this:

Select b.* from (select subject,max (score) m from grade GROUP BY subject) t grade b where t.subject=b.subject and t.m=b.score UNION

Select b.* from (select subject,min (score) m from grade GROUP BY subject) t grade b where t.subject=b.subject and t.m=b.score

The result is:

That's ok. If you want to add something else. For example, add a column indicating that the score is the lowest or the highest.

Select b. Score, "highest score" from (select subject,max (score) m from grade GROUP BY subject) t grade b where t.subject=b.subject and t.m=b.score

UNION

Select b. Score, "lowest score" from (select subject,min (score) m from grade GROUP BY subject) t grade b where t.subject=b.subject and t.m=b.score

This is the end of the content of "what is the use of group by in the database". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.