Group by, order by, having, where

Original article: http://hi.baidu.com/%D6%EC%D5%F2%B3%C7/blog/item/d58a2ecc7ebd942cf9dc6121.html

 

Order by is used to sort data. By default, it is ASC in ascending order, and desc is used in descending order.

Select select_list

 

[Into new_table]
From table_source
[Where search_condition]
[Group by group_by_expression]
[Having search_condition]

 

 

1. Group by is a group query. Generally, group by isUsed with aggregate Functions

The principle of group by is that columns without Aggregate functions in all columns after select must appear after group by (important)

For example, there are the following database tables:

A B
1 ABC
1 BCD

1 asdfg

If the following query statement (This statement is incorrect. For the reason, see the previous principles.)

Select a, B from Table group by

The intention of this query statement is to get the following results (Of course, it's just a willingness)

A B
ABC
1 BCD

Asdfg

How to convert the three entries on the right into one, so an aggregate function is required, as shown below (Which of the following statements is correct?):

Select a, count (B) as quantity from Table group by
The result is
A quantity
1 3

 

 

 

2. Having and where

Where: InBefore grouping query resultsRemove rows that do not meet the where condition, that is, filter data before grouping,The condition cannot contain clustering functions., Use the where ConditionShow specific rows.

Having is used to filter groups that meet the conditions, that isFilter data After grouping,Conditions often contain clustering functions., Using the having ConditionDisplay a specific group, You can also use multiple grouping standards for grouping.

The having clause is limited to columns and aggregate expressions defined in the SELECT statement. Generally, you need to repeat the aggregate function expression in the having clause to reference the aggregate value, just as you did in the SELECT statement. For example:

Select a count (B) from Table group by a having count (B)> 2