MySQL group and filter bucket where the combination of the use

To understand grouping, you can: group the column names followed by the GROUP BY clause, and then manipulate each grouping instead of the entire table.

For example: In the Product table, retrieve the quantity of goods supplied by each supplier.

Mysql> SELECT Vend_id,count (*) as num_prods from the products GROUP by vend_id;

Results: +---------+-----------+| vend_id | Num_prods |+---------+-----------+|    1001 |         3 | |    1002 |         2 | |    1003 |         7 | |    1005 |         2 |+---------+-----------+4 rows in Set (0.01 sec)

Analysis:

First, groups are grouped according to vend_id, and then count is aggregated for each grouping. When the purpose of the search is to retrieve each record, think of the group by, for example, for each vendor.

GROUP by provisions:

1, GROUP by can contain more than one column, which is nested.

2. If group BY is nested, the data is aggregated on the last group.

3. Each column listed in the GROUP BY clause must be a retrieval column or a valid expression (but not a clustered function), and if an expression is used in Select, the same expression must be specified in the GROUP BY clause. Aliases cannot be used.

4. In addition to the aggregation statement, each column in a SELECT statement must be given in the GROUP BY clause.

5. If there is a null value in the grouping column, NULL is returned as a grouping. If there are more than one NULL in the column, they are returned as a grouping.

6. The GROUP by clause must precede the WHERE clause before the ORDER by clause.

Filter grouping results

We know where clauses are used to filter the results, but not for grouped filter where clauses.

Because the WHERE clause is a filter for rows. To filter the grouped results, you must use the HAVING clause, which can be filtered for the result of the grouping.

Example:

In the order form, retrieve the customer ID and order quantity that issued more than two orders.

Analysis:

In this search requirement, you need to group the customer ID first, and then filter out the group with orders greater than 2.

Mysql> SELECT Cust_id,count (*) as orders from orders GROUP by cust_id have orders>=2;

　　

results:+---------+--------+| cust_id | Orders |+---------+--------+|   10001 |      2 |+---------+--------+1inset (0.00 sec)

Used in combination with where (where filtering is used first)

Sometimes the group BY and WHERE clauses are used together. For example: in the Product table to search for more than 2 products, and the price is higher than 10 of suppliers.

Analysis:

First, the vendor is retrieved, so the SELECT clause should be select VEND_ID

Second, there is a price column in the product table, so the WHERE clause is used to filter for conditions that are higher than 10. SELECT vend_id from Prodcuts WHERE prod_price>=10.

Next, vend_id are grouped so that the number of items per vend_id is higher than 10, and group by is placed after the WHERE clause. SELECT vend_id from Prodcuts WHERE prod_price>=10 GROUP by vend_id.

Finally, filtering the results of the grouping, filtering out the grouping of more than 2 items

Mysql> SELECT Vend_id,count (*) as Num_prods from Products WHERE prod_price>=ten GROUP by vend_id have Coun T (*) >=2;

Results: +---------+-----------+| vend_id | Num_prods |+---------+-----------+|    1003 |         4 | |    1005 |         2 |+---------+-----------+2 rows in Set (0.00 sec)

Sort the grouped results

In the order list, retrieve the order number with the total order price above or equal to 50 and the total order price

Mysql> SELECT order_num,sum (quantity*item_price) as OrderTotal from OrderItems GROUP by Order_num have SUM (quantity* Item_price) >= ORDER by OrderTotal;

The result is: +-----------+------------+| Order_num | OrderTotal |+-----------+------------+|     20006 |      55.00 | |     20008 |     125.00 | |     20005 |     149.87 | |     20007 |    1000.00 |+-----------+------------+4 rows in Set (0.08 sec)

　　

Order of the SELECT clause

SELECT

From

WHERE

GROUP by #分组

Having #过滤

ORDER by #

LIMIT #限制

　　

MySQL group and filter bucket where the combination of the use