Where, group by, and having usage parsing in SQL (excerpt)

Article Address Source:

Https://www.cnblogs.com/gqs92/archive/2017/04/26/6767973.html

the Where, group by, and having usages in--sql

If you want to use GROUP BY, the word "every" is used for example, there is now a table: How many people in each department are going to use the technology of grouping

Select DepartmentID as ' department name ', COUNT (*) as ' number ' from Basicdepartment GROUP by DepartmentID
-This is grouping by using the group by + field, where we can understand that we followed the department's name ID
--departmentid data sets are grouped, and then the statistics of each group are divided into numbers;
--If you do not use COUNT (*) with a syntax similar to the following
Select Departmentid,departmentname from Basicdepartment GROUP by DepartmentID

-There will be errors
--The column ' Basicdepartment.departmentname ' in the select list is not valid because the column is not contained in an aggregate function or a GROUP by clause.
That's what we need to be aware of, if in the return set field, these fields
It is either to be included behind the group by statement as a basis for grouping;
It should be included in the aggregation function as the basis for grouping;
--Explanation of the error: Let's take a look at the execution of group by, first performing a select operation returns an assembly,
--then go to the action of grouping, when he will be based on the field behind group by
--grouping, and dividing the same field into a column of data, if the group by is not followed by this field, it will be divided into a lot of data.
--but the grouping only divides the same data into two columns of data, and only one field in a column, so those that are not grouped
--The data system does not know where to put the data, so this error occurs
--there is only one record for a grouping, and a data grid cannot fit into multiple values.
--so it's necessary to convert these multivalued columns into single values, and then place them in the corresponding
--In the data grid, the aggregation function is the complete step. That's why these functions are called aggregate functions (aggregate functions).

--group by all syntax parsing:
--If you use the ALL keyword, the query results will include all groups produced by the GROUP BY clause, even if some groups do not have rows that match the search criteria.
--without the ALL keyword, a SELECT statement that contains a GROUP BY clause will not display a group that does not have a qualifying row.
Select Departmentid,departmentname as ' department name ',
COUNT (*) as ' number ' from Basicdepartment GROUP by all Departmentid,departmentname

==========================================================================================================

--group by and having interpretation: the premise must understand a special function in the SQL language: aggregate function,
such as Sum, COUNT, MAX, AVG, and so on. The fundamental difference between these functions and other functions is that they generally function on more than one record.

Having is the filter after grouping (group by), and then filtering within the grouped data group
Where is the filter before grouping

You cannot use the aggregate function in the WHERE clause, and the HAVING clause allows you to add a have in the collection function to test whether the query results match the condition.
The applicable scenario for having clauses is that you can use aggregate functions

The HAVING clause restricts the group, not the row
Each element in a HAVING clause must also appear in the select list. Some database exceptions, such as Oracle

When both the WHERE clause, the GROUP BY clause, the HAVING clause, and the aggregation function are included, the order of execution is as follows:
Executes a WHERE clause to find data that meets the criteria;
Use the GROUP BY clause to group the data, and the GROUP BY clause to run the aggregate function to calculate the value of each group; and finally to remove the non-conforming group with the HAVING clause

Ex: Shows the total population and area of each region. Only those areas with more than 1000000 area are shown.
SELECT region, sum (population), sum (area)
From BBC
GROUP by region
Have SUM (area) >1000000
Here, we cannot use where to filter more than 1000000 of the area, because there is no such record in the table. (aggregate functions cannot be used in a WHERE clause)
Instead, having clauses allows us to filter groups of data

Ex:create TABLE Table1
(
--ID int identity (primary) key not NULL,
ID int primary key not NULL,
ClassID int,
Sex varchar (10),
Age int
)
--Add test multiple data
Insert into Table1 values (1, 1, ' Male ', 20);
Insert into Table1 values (2,2, ' female ', 22);
Insert into Table1 values (3,3, ' Male ', 23);
Insert into Table1 values (+, ' male ', 22);
Insert into Table1 values (5,1, ' Male ', 24);
..........
Check the number of people who are older than 20 in each class and have a minimum of 2 male gender
Select count (age) as ' >20 number of years ', ClassID from Table1 where sex= ' Men ' GROUP by ClassID have COUNT (ages) >2

The bottom example is good.
Sql> SELECT * from SC;

SNO PNO GRADE
---------- ----- ----------
YW 95
SX 98
YY 90
YW 89
SX 91
YY 92
YW 85
SX 88
YY 96
YW 95
SX 89
YY 88

This table describes the records of 4 students corresponding to each subject, including SNO (student number), PNO (course name), GRADE (score).

1. Show the course name and achievement of 90 + students

This is a simple query and does not use a group query

Sql> Select Sno,pno,grade from SC where grade>=90;

SNO PNO GRADE
---------- ----- ----------
YW 95
SX 98
YY 90
SX 91
YY 92
YY 96
YW 95

2. Show how many doors each student has scored in more than 90 points

--group display, followed by a Where condition count

Sql> Select Sno,count (*) from SC where grade>=90 GROUP by Sno;

SNO COUNT (*)
-    --------- ----------
3
2
1
1

3, here we do not use having the statement, next if we want to award Miyoshi students, the condition is at least two courses in 90 points above to be eligible,
List of eligible student numbers and 90 + courses.

is grouped, and is counted after the where condition, and the grouping is filtered by the HAVING clause

Sql> Select Sno,count (*) from SC where grade>=90 GROUP by SNO have Count (*) >=2;

SNO COUNT (*)
---------- ----------
3
2

This result is what we want, it lists the number of students who have the Miyoshi students qualification, followed by an example of the comparison, found that this is a sub-query after grouping.

4, the school selection of advanced students, the average score of more than 90 students are eligible, and the language class must be more than 95 points, please list qualified students

In fact, this query first to the language of more than 95 points of the student number extracted, after the average, after the group display according to having a sentence to choose the average score of more than 90

Sql> Select Sno,avg (grade) from SC where Sno in (select Sno from SC where grade>=95 and pno= ' YW ') GROUP by Sno Havin G avg (Grade) >=90;

SNO AVG (GRADE)
---------- ----------
94.3333333
90.6666667

5, the query than the average score is at least 3 of the average grade of students with high academic number and average scores

Comparison and subquery can be made in the HAVING clause
Sql> Select Sno,avg (grade) from SC
GROUP BY Sno
have avg (grade) > (select AVG (grade) from SC where sno=3);

Where, group by, and having usage parsing in SQL (excerpt)