mysql 篩選后分組查詢

發(fā)布時間：2020-07-08 02:41:27 來源：網(wǎng)絡(luò) 閱讀：239 作者：wx5d21d5e6e5ab1 欄目：MySQL數(shù)據(jù)庫

語法:
select 分組函數(shù)，列（要求出現(xiàn)在group by的后面）
from 表
where ..
group by 分組的列表
order by ...

#案例：查詢各個部門的平均工資

SELECT AVG(salary),department_id
FROM employees
GROUP BY
    department_id
ORDER BY
    department_id DESC;

#案例:查詢每個工種的最高工資

SELECT
    MAX(salary),job_id
FROM
    employees
GROUP BY
    job_id;

#案例:查詢每個部門的位置個數(shù)

SELECT
    COUNT(location_id),department_name
FROM
    departments
GROUP BY
    department_name;

#案例:查詢郵箱中包含a字符的，每個部門的平均工資

SELECT
    AVG(salary),department_id
FROM
    employees
WHERE
    email LIKE('%a%')
GROUP BY
    department_id;

#案例：查詢有獎金的每個領(lǐng)導(dǎo)手下員工的最高工資

SELECT
    MAX(salary),manager_id
FROM 
    employees
WHERE
    commission_pct IS NOT NULL
GROUP BY 
    manager_id;

向AI問一下細(xì)節(jié)

猜你喜歡