mysql group by having 執行個體代碼_Mysql

mysql group by having 執行個體

注意：使用group by的時候，SELECT子句中的列名必須為分組列。 如下執行個體必須包括name列名，因為name是作為group by分組的條件。

執行個體：

我的資料庫中有一張員工工作記錄表，表中的資料庫如下：

mysql> SELECT * FROM employee_tbl;+------+------+------------+--------------------+| id  | name | work_date | daily_typing_pages |+------+------+------------+--------------------+|  1 | John | 2007-01-24 |        250 ||  2 | Ram | 2007-05-27 |        220 ||  3 | Jack | 2007-05-06 |        170 ||  3 | Jack | 2007-04-06 |        100 ||  4 | Jill | 2007-04-06 |        220 ||  5 | Zara | 2007-06-06 |        300 ||  5 | Zara | 2007-02-06 |        350 |+------+------+------------+--------------------+7 rows in set (0.00 sec)

需求一：現在我需要尋找出每個員工工作了多少天，這時就需要用到group by語句：

mysql> SELECT name, COUNT(*)  -> FROM  employee_tbl   -> GROUP BY name;+------+----------+| name | COUNT(*) |+------+----------+| Jack |    2 || Jill |    1 || John |    1 || Ram |    1 || Zara |    2 |+------+----------+5 rows in set (0.04 sec)

需求二：我現在要尋找工作天數大於一天的所有員工，這個時候就需要用到group by having語句

mysql> SELECT name, COUNT(*)  -> FROM  employee_tbl   -> GROUP BY name having COUNT(*)>1 ;+------+----------+| name | COUNT(*) |+------+----------+| Jack |    2 || Zara |    2 |+------+----------+2 rows in set (0.04 sec)

感謝閱讀，希望能協助到大家，謝謝大家對本站的支援！