P40 group by的使用

2.group by的使用

#需求：查询各个部门的平均工资，最高工资
SELECT department_id,AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id

#需求：查询各个job_id的平均工资
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id;

#需求：查询各个department_id,job_id的平均工资
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;

#错误的，一个部门只显示一个工种了
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id;
#结论1：select中出现的非组函数的字段，必须要出现在group by 中
#      反之group by中声明的字段，可以不出现在select中
#结论2：group by 声明在from后面、where后面，order by前、limit前
#结论3：MySQL中group by中使用with rollup
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;

#需求：查询各个部门的平均工资，按照平均工资升序排列
SELECT department_id,AVG(salary)"avg_sal"
FROM employees
GROUP BY department_id 
ORDER BY avg_sal ASC;
#说明：当使用ROLLUP时，不能同时使用order by
SELECT department_id,AVG(salary)"avg_sal"
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;