分组查询

SELECT count(),department_id from employees GROUP BY department_id HAVING count()>2;

#按表达式进行分组
SELECT count() ,LENGTH(last_name) from employees GROUP BY LENGTH(last_name) HAVING count()>5 ORDER BY LENGTH(last_name) asc;

#按多个字段分组
SELECT AVG(salary) ,department_id,job_id from employees GROUP BY department_id,job_id HAVING AVG(salary)>5000;

#分组查询排序
SELECT AVG(salary) ,department_id,job_id from employees where department_id is not NULL GROUP BY department_id,job_id HAVING AVG(salary)>10000 ORDER BY AVG(salary) DESC;

SELECT max(salary)-min(salary) from employees

SELECT min(salary) , manager_id from employees where manager_id is not null GROUP BY manager_id HAVING min(salary)>6000

SELECT department_id ,count(*),ROUND(AVG(salary),2) from employees where department_id is not null GROUP BY department_id ORDER BY AVG(salary) desc;

SELECT count(*),job_id from employees where job_id is not null GROUP BY job_id