MYSQL學習筆記8: DQL分組查詢(group by)

DQL分組查詢(group by)

語法

select 欄位列表 from 表名 [where 條件] group by 分組欄位名 [having 分組後過濾條件];

where和having的區別

• 執行時機不同: where是在分組之前進行過濾,不滿足where條件,不參與分組; having是分組之後對結果進行過濾

• 判斷條件不同:where不能對聚合函式進行判斷,having可以

---

根據員工性別分組, 統計男女員工數量

select gender, count(*) from workers group by gender;

根據性別分組,並統計平均年齡

select gender, avg(age) from workers group by gender;

查詢年齡>45的員工, 並按入職日期分組, 獲取員工數量>=2的入職日期

select entryDate, count(*) dateCount
    from workers
    where age>45
    group by entryDate
    #分組後過濾
    having dateCount>=2;