You can understand group by and Aggregate functions in this way, groupby

You can understand group by and Aggregate functions in this way, groupby
Turn http://www.cnblogs.com/wuguanglei/p/4229938.html? Utm_source = tuicool; I personally think it is quite useful.

In front of the article: It took a long time for group by to wake up early today, and suddenly I felt a strange group by, and there was always a barrier, why can't I select * from Table group by id? Why cannot I be *? Instead, it is an aggregate function of a column or a column, how can multiple fields of group by be well understood? But I finally turned around. Let's simply write it. Let's just skip it.

========= Start of the body ==================

First, let's look at table 1, the table name is test:

 

Table 1

Run the following SQL statement:

12	SELECT name FROM testGROUP BY name

You should easily know the running result. That is, table 2:

 

Table 2

However, in order to better understand"Group by" multiple columns"And"Aggregate function"In the process of thinking, I suggest adding a fictitious intermediate table from table 1 to table 2:Virtual table 3. The following describes how to consider the preceding SQL statement execution:

1. FROM test: after the sentence is executed, the result should be the same as table 1, which is the original table.

2. FROM test Group BY name: After this sentence is executed, we can imagine thatVirtual table 3,As shown in the figure below, the generation process is like this: group by name, then find the name column, and combine the rows with the same name value into one row, for example, if the name value is aa, the <1 aa 2> and <2 aa 3> rows are merged into one row, and all the id and number values are written into one cell.

 

3. Next we will focus onVirtual table 3The Select statement is executed:

(1) If select * is executed, the returned result is virtual table 3, but the content in the cells of id and number is multiple values, relational databases are relational-based and cannot contain multiple values in cells. Therefore, if you execute the select * Statement, an error is returned.

(2) Let's look at the name column again. Each cell has only one data, so if we select name, there will be no problem. Why is there only one value for each cell in the name column, because we use the name column for group.

(3) What if there are multiple data records in the cell id and number? The answer is to useAggregate functions are used to input multiple data and output one data.For example, cout (id) and sum (number), and the input of each aggregate function is the cell of every multi-data.

(4) For example, if we execute select name, sum (number) from test group by name, sum will perform the sum operation on each cell in the number column of table 3, for example, execute the sum operation on the number column of the row whose name is aa, that is, 2 + 3, and return 5. The execution result is as follows:

(5) How do I understand multiple fields of group by: for example, group by name and number, we can regard name and number as oneOverall FieldAnd groups them as a whole. For example

(6) Then we can work with select and Aggregate functions. For example, run select name, sum (id) from test group by name, number. The result is as follows:

So far, I have been overwhelmed by my own thoughts on such a simple problem, and I want to wash my clothes and sleep.

(End)

Document Information 

• Copyright Disclaimer: Reprinted freely-non-commercial-non-derivative-keep the signature (creative sharing 3.0 License)
• This article is an original article. You are welcome to repost it. This blog will be updated continuously in the future. Therefore, please keep this document information.
• Address: http://www.cnblogs.com/wuguanglei/p/4229938.html