按表达式或函数分组:
- # 案例: 按员工的姓名的长度分组, 查询每一组的员工个数, 筛选员工个数大于五
- SELECT
- COUNT(*),LENGTH(last_name)
- FROM
- employees
- GROUP BY
- LENGTH(last_name)
- HAVING
- COUNT(*)>5;
按多个字段分组:
- # 案例: 查询每个部门每个工种的员工的平均工资
- SELECT
- AVG(salary),department_id,job_id
- FROM
- employees
- GROUP BY
- department_id,job_id;
排序
- # 案例: 查询每个部门每个工种的部门编号不为 null 的员工的平均工资 > 10000, 并且按平均工资的高低显示
- 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;
来源: http://www.bubuko.com/infodetail-3189311.html