group by and having
Group by: group by a certain field or some fields.
Having: having is to filter the grouped data again
Find out the highest salary for each job.
This needs to be grouped, because you are not looking for all the maximum salary, and the maximum salary of each job. Grouping functions are always performed after group by
Yes, select max (sal) from emp group by job;
Note: grouping functions are generally used in conjunction with group by, which is why they are called grouping functions. Any grouping function (count, sum, avg, max, min) ends when the group by statement is executed.
If there is no group by in an sql statement, the data of the whole table will form a group by itself
group by is executed only after the execution of where, so the grouping function cannot be used after where
Multi field query
select name,max(sal),job from emp group by job;
Note: the above statements have query results in mysql, but the results are meaningless; errors and syntax errors will be reported in oracle database. The syntax rules of oracle are more rigorous than those of MySQL.
Remember a rule: when there is group by in a statement, select can only be followed by grouping functions and fields participating in grouping
Therefore, the above code can only be select max(sal).job from emp group by job;
This is the average salary for each job.
Can multiple fields be grouped together?
Find the maximum number of different type s (one field grouping)
mysql> select max(num),type from scan_problem_info_item -> group by type; +----------+------+ | max(num) | type | +----------+------+ | 4556 | 1 | | 33 | 2 | | 45 | 3 | | 22 | 4 | | 21 | 5 | | 45 | 8 | +----------+------+ 6 rows in set (0.03 sec)
Group multiple fields.
Case list: find out the highest salary for different jobs in each department.
select deptno.job,max(sal) from emp group by deptno,job;
having
Find out the maximum number of different type s and require data greater than 40
mysql> select max(num),type from scan_problem_info_item -> group by type having max(num)>40; +----------+------+ | max(num) | type | +----------+------+ | 4556 | 1 | | 45 | 3 | | 45 | 8 | +----------+------+ 3 rows in set (0.04 sec)
If you write this way, the efficiency will be a little low. It is recommended to use where for filtering, because where can be directly filtered to improve efficiency. Where is executed first than group by. Therefore, it is more efficient to use where. It is recommended to use where as far as possible if you can use where
mysql> select max(num),type from scan_problem_info_item -> where num > 40 -> group by type; +----------+------+ | max(num) | type | +----------+------+ | 45 | 3 | | 45 | 8 | | 4556 | 1 | +----------+------+ 3 rows in set (0.00 sec)
But there are also cases where you can't decide. For example, find out the data that the average salary of type is greater than 30.
mysql> select avg(num),type from scan_problem_info_item -> group by type -> having -> avg(num)>30; +-----------+------+ | avg(num) | type | +-----------+------+ | 2289.5000 | 1 | | 32.5000 | 2 | +-----------+------+ 2 rows in set (0.04 sec)
Grouping function cannot be added after where. But where is used in the above because num is not calculated after grouping, and the average is calculated after grouping
So you must use having.
Execution order of grouping functions:
Query data according to criteria
grouping
Using having filtering to obtain correct data
summary
How to write a complete DQL statement?
select ...... ⑸ from ...... ⑴ where ...... ⑵ group by ...... ⑶ having ...... ⑷ order by ...... ⑹