MySQL(15)——分组查询（按函数分组）

按表达式或函数分组

（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;