5.sql2008 Grouping and nesting

1.Group by basic introduction;
2.Having of Use;
3. Group comprehensive Application;
4. Sub-query basic introduction;
5.in/exists/any/some/all;
6. Sub-query comprehensive application;

1.Group by Basic: The DataSet is divided into several small areas according to the rules provided by the by, and then the small areas are processed.
--Recognize the essence: by specifying the fields will be the same group into a set, so there will be a number of small collections. The aggregate function is then used to count the specified fields (not specified as *).
A. How many men and women are the clients:
Select Csex as ' Gender ', COUNT (*) as ' number '
--Once a group can write only grouped fields and aggregate functions, no other fields can be written in the middle.
From customer
GROUP BY gender

B. To count the highest prices in each category of commodity;
Select Product ID, max (commodity price) as ' highest price '
From Product List
GROUP BY Product ID

C. Find out the number of goods with the most quantity sold;
Select Product Id,count (*)
From schedule
GROUP BY Product ID
Order BY Count (*) desc
This only sells the most sort. should be nested one more time:
Select Product Id,count (*)
From schedule
GROUP BY Product ID
Having count (*) =
(
Select Tip 1 Count (*)
From schedule
GROUP BY Product ID
Order BY Count (*) desc
)
GROUP BY all
Example: 2016 years of customer shopping:
Select CNO, COUNT (*) as ' shopping quantity '
From schedule
Where shopping date between ' 2016-1-1 ' and ' 2016-12-31 '
GROUP BY CNO
Order BY COUNT (*)//Sort

If you add all, you will also be counted for not shopping in this time period.

Example: Count the number of customers who purchase more than two items, sorted in descending order of quantity.
Analysis: You need to use the Customer table and the shopping list two tables, using the grouping to find the customer number and the number greater than 2, and then the two tables associated with the query results. (You must use the HAVING clause to filter the grouped data.)

Select Cname,t2. Shopping Volume field
From Customer T1,
(
Select Cno,count (*)
From schedule
GROUP BY CNO
Having Count (*) >2
) T2
where T1.cno=t2.cno
ORDER by T2. Shopping volume Field desc
!!! When you do a group query, the SELECT statement can be followed only by a grouped field name or aggregate function.

Nested queries:
can be nested in select, nested in from, and nested in where.
Example 1. Count which customers have purchased how many items. (Select,from)

Example 2. Query the name of the product that sells the most. (where)

5.sql2008 Grouping and nesting