按表达式或函数分组
(1)按员工姓名(last_name)分组,查询每一组的员工个数,筛选满足员工个数大于5的
SELECT
COUNT(*) c,
LENGTH(last_name) len_name
FROM
JDSC
GROUP BY
len_name
HAVING
c>5;
按多个字段分组
(2)查询每个部门(department_id)、每个工种(job_id)的员工的平均工资(salary)
SELECT
AVG(salary),
department_id,
job_id
FROM
JDSC
GROUP BY
department_id,
job_id;
添加排序
(3)查询每个部门(department_id)、每个工种(job_id)的员工的平均工资(salary),并且按平均工资大于10000的高低排序
SELECT
AVG(salary) a,
department_id,
job_id
FROM
JDSC
WHERE
department_id IS NOT NULL
GROUP BY
department_id,
job_id
HAVING
a>10000
ORDER BY
a DESC;