Analysis of the combined use of aggregate functions and connection tables _ MySQL

Analyze the combination of aggregate functions and connection tables using bitsCN.com

Analyze the combined use of aggregate functions and connection tables

Look at this relationship

MERS (cust_id, cust_name, cust_contact)

Orders (order_num, cust_id, order_date)

If there is such a search statement:

SQL code

Select cust_name, cust_contact ,(

Select count (*) from Orders

Where Orders. cust_id = Customers. cust_id

Group by cust_id)

From MERS

The procedure is as follows:

1. retrieve the order quantity of each cust_id from the Orders table

This can be done because the isolated count (*) is aggregated based on the group by customer number.

In addition, the join table operation is performed before aggregation. Connect orders and MERS.

In this way, only the customer information is agreed.

2. the outer layer lists other required customer information.

Note:

The inner layer is usually used as an isolated aggregate function,

Before grouping operations on the inner layer, the outer layer must be connected.

The selection columns behind the outer select are basically in the outer table.

To sum up, is this sentence a subquery or a normal connection.

Obviously. It is a subquery that contains a select sentence in the select statement.

Very special. clauses can be used as a search column !~ That's it.

In the future, we will try to group but involve two different tables.

BitsCN.com