MySQL advanced query: Functions and keywords used in the GroupBy set _ MySQL

MySQL advanced query: Functions and keywords used in the GroupBy set bitsCN.com

1GROUP_CONCAT

Mysql>SELECT student_name,

->GROUP_CONCAT (test_score)

->FROM student

->Group by student_name;

Or:

Mysql>SELECT student_name,

->GROUP_CONCAT (DISTINCT test_score

->Order by test_score desc separator '')

->FROM student

->Group by student_name;

In MySQL, you can obtain the join value of expression combinations. You can use DISTINCT to delete duplicate values. If you want to sort multiple result values, use the order by clause. To sort in reverse ORDER, add the DESC (descending) keyword to the column name that you want to sort using the order by clause. The default order is ascending. you can use ASC to specify it. SEPARATOR follows the string value in the middle of the value of the inserted result. The default value is comma (','). You can delete all separators by specifying the SEPARATOR.

PS: it is a string that can be used to obtain a combination of each sub-value of the group by aggregated item in a statement.

2WITH ROLLUP

The group by clause allows an additional row to be added to the simple output end WITH the ROLLUP modifier. These rows represent high-level (or high aggregation) simple operations. ROLLUP allows you to answer questions from the perspective of multi-layer analysis.

Alternatively, you can use ROLLUP, which provides a double-layer analysis with an inquiry. Add a modifier with rollup to the group by statement to generate another row of results for the query. this row shows the total value of all years:

Mysql>SELECT year, SUM (profit) FROM sales group by year with rollup;

+ ------ + ------------- +

| Year | SUM (profit) |

+ ------ + ------------- +

| 2000/4525 |

| 2001/3010 |

| NULL | 7535 |

+ ------ + ------------- +

The total high clustering rows are marked by the NULL value in the year column.

When multiple group by columns exist, ROLLUP produces more complex results. At this time, each time a "break" appears in any column except the last classification column (value change), the inquiry will generate a high clustering cumulative row.

For example, in the absence of ROLLUP, a yearly, country, and product-based sales table list may be as follows:

Mysql>SELECT year, country, product, SUM (profit)

->FROM sales

->Group by year, country, product;

+ ------ + --------- + ------------ + ------------- +

| Year | country | product | SUM (profit) |

+ ------ + --------- + ------------ + ------------- +

| 2000 | Finland | Computer | 1500 |

| 2000 | Finland | Phone | 100 |

| 2000 | India | Calculator | 150 |

| 2000 | India | Computer | 1200 |

| 2000 | USA | Calculator | 75 |

| 2000 | USA | Computer | 1500 |

| 2001 | Finland | Phone | 10 |

| 2001 | USA | Calculator | 50 |

| 2001 | USA | Computer | 2700 |

| 2001 | USA | TV | 250 |

+ ------ + --------- + ------------ + ------------- +

Indicates that the output result of the total value is only at the analysis level of the year/country/product. After ROLLUP is added, the query will generate some additional rows:

Mysql>SELECT year, country, product, SUM (profit)

->FROM sales

->Group by year, country, product with rollup;

+ ------ + --------- + ------------ + ------------- +

| Year | country | product | SUM (profit) |

+ ------ + --------- + ------------ + ------------- +

| 2000 | Finland | Computer | 1500 |

| 2000 | Finland | Phone | 100 |

| 2000 | Finland | NULL | 1600 |

| 2000 | India | Calculator | 150 |

| 2000 | India | Computer | 1200 |

| 2000 | India | NULL | 1350 |

| 2000 | USA | Calculator | 75 |

| 2000 | USA | Computer | 1500 |

| 2000 | USA | NULL | 1575 |

| 2000 | NULL | 4525 |

| 2001 | Finland | Phone | 10 |

| 2001 | Finland | NULL | 10 |

| 2001 | USA | Calculator | 50 |

| 2001 | USA | Computer | 2700 |

| 2001 | USA | TV | 250 |

| 2001 | USA | NULL | 3000 |

| 2001 | NULL | 3010 |

| Null| NULL | 7535 |

+ ------ + --------- + ------------ + ------------- +

When you use ROLLUP, you cannot use the order by clause to sort the results at the same time. In other words, ROLLUP and order by are mutually exclusive. However, you can still control the sorting. In MySQL, group by can sort the results, and you can use clear ASC and DESC keywords in the columns specified in the group by list to sort individual columns. (No matter how you sort the high-level total rows added by ROLLUP, they still appear after the rows they are calculated ).

LIMIT can be used to LIMIT the number of lines returned to the client. LIMIT is used after ROLLUP, so this LIMIT will cancel the row added by ROLLUP

BitsCN.com