sql group by 子句和having 子句

mysql> select  e. 学历, xs.性别, count(xs.性别) AS 人数
    -> from  employees e
    -> join xs on e.姓名 = xs.姓名
    -> group by e.学历, xs.性别
    ->  with rollup;
+------+------+------+
| 学历    | 性别    | 人数     |
+------+------+------+
| 大专    |    1 |    2 |
| 大专    | NULL |    2 |
| NULL | NULL |    2 |
+------+------+------+
3 rows in set (0.00 sec)

mysql> select left (电话, 1) as 电话首位,
    ->        count(*) AS 人数
    -> from employees
    -> group by 电话首位;
+----------+------+
| 电话首位      | 人数     |
+----------+------+
| 3        |    2 |
| 5        |    2 |
| 6        |    1 |
| 8        |    8 |
+----------+------+
4 rows in set (0.00 sec)


mysql> select d.部门名称, avg(s.收入) AS 平均收入
    -> from departments d
    -> join employees e on d.部门号 = e.部门号
    -> join (
    ->     select s.编号, s.收入
    ->     from salary s
    ->     join employees e on s.编号 = e.编号
    -> ) s on e.编号 = s.编号
    -> group by d.部门名称;
+------------+-------------------+
| 部门名称           | 平均收入                 |
+------------+-------------------+
| 人力资源部          | 2100.800048828125 |
| 市场部          | 2004.296630859375 |
| 研发部          | 2526.276611328125 |
| 经理办公室         | 2749.989990234375 |
| 财务部           | 2147.574981689453 |
+------------+-------------------+
5 rows in set (0.00 sec)

mysql> SELECT MONTH(出生日期) AS 出生月, COUNT(*) AS 人数
    -> FROM xs
    -> GROUP BY 出生月
    -> ;
+--------+------+
| 出生月       | 人数     |
+--------+------+
|      1 |    2 |
|      2 |    2 |
|      3 |    1 |
|      5 |    1 |
|      6 |    1 |
|      8 |    2 |
|      9 |    1 |
|     10 |    2 |
+--------+------+
8 rows in set (0.00 sec)


mysql> select xs.学号, xs.姓名, xs_kc.课程号, xs_kc.成绩
    -> from xs
    -> join xs_kc on xs.学号 = xs_kc.学号
    -> where xs.学号 in (
    ->     select 学号
    ->     from xs_kc
    ->     group by 学号
    ->     having count(课程号) > 1
    -> )
    -> and xs_kc.成绩 between 60 and 69
    -> order by xs.学号, xs_kc.课程号;
+--------+--------+--------+------+
| 学号      | 姓名       | 课程号     | 成绩    |
+--------+--------+--------+------+
| 081203 | 严红      | 301    |   68 |
| 081203 | 严红      | 302    |   60 |
| 081303 | 孙祥欣       | 101    |   63 |
| 081304 | 孙研       | 101    |   65 |
+--------+--------+--------+------+
4 rows in set (0.00 sec)