mysql 按表達(dá)式或函數(shù)分組、多個(gè)字段分組、排序

發(fā)布時(shí)間：2020-07-20 15:06:11 來(lái)源：網(wǎng)絡(luò) 閱讀：530 作者：wx5d21d5e6e5ab1 欄目：MySQL數(shù)據(jù)庫(kù)

按表達(dá)式或函數(shù)分組：

#案例：按員工的姓名的長(zhǎng)度分組，查詢每一組的員工個(gè)數(shù)，篩選員工個(gè)數(shù)大于五

SELECT
    COUNT(*),LENGTH(last_name)
FROM
    employees
GROUP BY
    LENGTH(last_name)
HAVING
    COUNT(*)>5;

按多個(gè)字段分組：
#案例：查詢每個(gè)部門每個(gè)工種的員工的平均工資

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

排序
#案例：查詢每個(gè)部門每個(gè)工種的部門編號(hào)不為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;

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

mysql 按表達(dá)式或函數(shù)分組、多個(gè)字段分組、排序

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽

mysql 按表達(dá)式或函數(shù)分組、多個(gè)字段分組、排序