分组

 一.分组

SELECT column, group_function(column)
FROM table
[WHEREcondition]
[GROUP BY group_by_expression]
[ORDER BYcolumn];

1.SELECT
  department_id, AVG(salary)
  FROM employees
  GROUP BY department_id ;

2.SELECT
  AVG(salary)
  FROM employees
  GROUP BY department_id

3.SELECT department_id dept_id, job_id, SUM(salary)
  FROM employees
  GROUP BY department_id, job_id ;  //多列来分组,有点向联合主键

二.HAVING (限制分组)

SELECT column, group_function
FROM table
[WHEREcondition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BYcolumn];

1.SELECT department_id, AVG(salary)
  FROM employees
  WHERE AVG(salary) > 8000     //错误的句子 WHERE 不能限制分组,只能用HAVING,如下:
  GROUP BY department_id;

1.SELECT department_id, AVG(salary)
  FROM employees
  HAVING AVG(salary) > 8000
  GROUP BY department_id;

2.SELECT department_id, MAX(salary)
  FROM employees
  GROUP BY department_id
  HAVING MAX(salary)>10000 ;

3.SELECT job_id, SUM(salary) PAYROLL
  FROM employees
  WHERE job_id NOT LIKE '%REP%'
  GROUP BY job_id
  HAVING SUM(salary) > 13000
  ORDER BY SUM(salary);

3.SELECT MAX(AVG(salary))  //嵌套组函数
  FROM employees
  GROUP BY department_id;

小结:
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];