Shulou(Shulou.com)06/01 Report--

The editor will share with you how to use group by in mysql. I hope you will gain a lot after reading this article. Let's discuss it together.

The use of group by in mysql is combined with aggregate functions, using grouping information for statistics, such as "select name,sum (id) from test group by name,number".

Let's take a look at Table 1 below, which is named test:

Execute the following SQL statement:

SELECT name FROM test GROUP BY name

It should be easy for you to know the results of the operation, yes, as shown in Table 2 below:

However, in order to better understand the application of "group by" multiple columns and "aggregate function", I suggest adding a fictional intermediate table: virtual Table 3 in the process of thinking, from Table 1 to Table 2. Here's how to think about the execution of the above SQL statement:

1.FROM test: after the execution of this sentence, the result should be the same as Table 1, which is the original table.

2.FROM test Group BY name: after the execution of this sentence, we imagine that virtual table 3 is generated. As shown in the following figure, the generation process is as follows: group by name, then find the column name, the row with the same name value, merge into a row, for example, for the name value aa, merge with two rows into one row, and all the id and number values are written into one cell, as shown in the following figure.

3. The next step is to execute the Select statement against virtual table 3:

(1) if you execute select *, then the returned result should be virtual table 3, but some cells in id and number contain multiple values, then id and number will return the first sorted value in their respective cells. The id column will return 1pm 3pm 5.

(2) if we look at the name column, there is only one data per cell, so if we select name, there will be no problem. Why does the name column have only one value per cell? because we use the name column to group by.

(3) what if there is more than one data in the cells in id and number? The answer is to use aggregate functions, which are used to input multiple data and output one data. Such as count (id), sum (number), and the input of each aggregate function is each multi-data cell.

(4) for example, if we execute select name,sum (number) from test group by name, then sum performs a sum operation on each cell of the number column of virtual table 3, for example, performing a sum operation on the number column of the row whose name is aa, that is, 2x3, which returns 5. The final execution result is as follows:

5) how to understand multiple fields of group by: for example, group by name,number, we can treat name and number as a whole field and group them as a whole, as shown in the following figure:

6) then you can work with select and aggregate functions. If you execute select name,sum (id) from test group by name,number, the result is as follows:

After reading this article, I believe you have a certain understanding of how to use group by in mysql, want to know more about it, welcome to follow the industry information channel, thank you for reading!

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.