一个发现：虽然 SELECT 的执行顺序在 GROUP BY 和 HAVING 后面，但对于SELECT中列的别名都可以使用。
MySQL中
1. > SELECT COUNT(*) as num, role_main, AVG(hp_max) FROM heros
    -> WHERE hp_max>6000
    -> GROUP BY role_main
    -> HAVING COUNT(*)>5
    -> ORDER BY COUNT(*) DESC;
+-----+-----------+-------------+
| num | role_main | AVG(hp_max) |
+-----+-----------+-------------+
| 17 | 战士 | 7028 |
| 10 | 坦克 | 8312.4 |
| 6 | 法师 | 6417 |
+-----+-----------+-------------+
2. > SELECT COUNT(*) num, ROUND(AVG(hp_max+mp_max), 2) avg, ROUND(MAX(hp_max+mp_max), 2) max, ROUND(MIN(hp_max+mp_max), 2) min FROM heros
    -> WHERE (hp_max+mp_max)>7000
    -> GROUP BY attack_range
    -> ORDER BY num DESC;
+-----+---------+----------+---------+
| num | avg | max | min |
+-----+---------+----------+---------+
| 36 | 8654.42 | 11036.00 | 7117.00 |
| 26 | 7743.77 | 8737.00 | 7025.00 |
+-----+---------+----------+---------+

练习题
1.SELECT COUNT(*) AS num,role_main,AVG(hp_max) FROM heros WHERE hp_max > 6000 GROUP BY role_main HAVING num>5 ORDER BY num DESC;
2.SELECT COUNT(*) AS num,ROUND(MAX(hp_max+mp_max),2),ROUND(AVG(hp_max+mp_max),2),ROUND(MIN(hp_max+mp_max),2) FROM heros WHERE hp_max+mp_max > 7000 GROUP BY attack_range ORDER BY num DESC;

/*
1.筛选最大生命值大于6000的英雄，按照主要定位进行分组，选择分组英雄数量大于5的分组，
按照分组英雄数从高到低进行排序，并显示每个分组的英雄数量、主要定位和平均最大生命值。
*/
SELECT count(*) as num, role_main, AVG(hp_max)
FROM heros
WHERE hp_max > 6000
GROUP BY role_main
HAVING num > 5
ORDER BY num DESC

num role_main AVG(hp_max)
------------------------------------
17    战士    7028
10    坦克    8312.4
6    法师    6417

/*
2.筛选最大生命值与最大法力值之和大于7000的英雄，按照攻击范围来进行分组，
显示分组的英雄数量，以及分组英雄的最大生命值与法力值之和的平均值、最大值和最小值，
并按照分组英雄数从高到低进行排序，其中聚集函数的结果包括小数点后两位。
*/
SELECT count(*) as num, ROUND(AVG(hp_max + mp_max), 2), MAX(hp_max + mp_max), MIN(hp_max + mp_max)
FROM heros
WHERE hp_max + mp_max > 7000
GROUP BY attack_range
HAVING num > 5
ORDER BY num DESC

num, ROUND(AVG(hp_max + mp_max), 2), MAX(hp_max + mp_max), MIN(hp_max + mp_max)
------------------------------------------------------------------------
62    8272.53    11036    7025

1.SELECT COUNT(*) as num,role_main,AVG(hp_max) FROM heros WHERE hp_max > 6000 GROUP BY role_main HAVING num>5 ORDER BY num DESC;
2.SELECT COUNT(*) as num,ROUND(MAX(hp_max+mp_max),2),ROUND(AVG(hp_max+mp_max),2),ROUND(MIN(hp_max+mp_max),2) FROM heros WHERE (hp_max+mp_max) > 7000 GROUP BY attack_range ORDER BY num DESC;
老师在“如何对数据进行分组，并进行聚集统计”的第三个例子里，COUNT(*) 后面没有加 as num，但是 ORDER BY 里直接出现了 num？

有个错误：
文中“比如，我们想要按照英雄的主要定位、次要定位进行分组，查看这些英雄的数量，并按照这些分组的英雄数量从高到低进行排序。”的SQL语句：SQL: SELECT COUNT(*), role_main, role_assist FROM heros GROUP BY role_main, role_assist ORDER BY num DESC

在MySQL里会报错：[Err] 1054 - Unknown column 'num' in 'order clause'

要改为：SELECT COUNT(*) as num, role_main, role_assist FROM heros GROUP BY role_main, role_assist ORDER BY num DESC;

思考题 1：
SELECT
    COUNT(*) AS num, role_main, AVG(hp_max)
FROM
    heros
WHERE
    hp_max > 6000
GROUP BY role_main
HAVING num > 5
ORDER BY num DESC;
思考题 2：
SELECT
    COUNT(*) AS num,
    attack_range,
    ROUND(AVG(hp_max + mp_max), 2),
    ROUND(MAX(hp_max + mp_max), 2),
    ROUND(MIN(hp_max + mp_max), 2)
FROM
    heros
WHERE
    hp_max + mp_max > 7000
GROUP BY attack_range
ORDER BY num DESC;

练习题
SELECT COUNT(*) as sum, role_main, AVG(hp_max) FROM heros WHERE hp_max > 6000 GROUP BY role_main HAVING sum > 5 ORDER BY sum DESC

SELECT COUNT(*) as num, ROUND(AVG(hp_max+mp_max),2), ROUND(MAX(hp_max+mp_max),2), ROUND(MIN(hp_max+mp_max),2) FROM heros WHERE hp_max+mp_max > 7000 GROUP BY attack_range ORDER BY num DESC

```sql
SELECT COUNT(*) as count, AVG(hp_max), role_main FROM heros WHERE hp_max > 6000 GROUP BY role_main HAVING count > 5 ORDER BY count desc;
```

```sql
SELECT COUNT(*) as count, ROUND(AVG(hp_max+mp_max), 2), ROUND(MAX(hp_max+mp_max), 2), ROUND(MIN(hp_max+mp_max), 2), attack_range FROM heros WHERE (hp_max+mp_max) > 7000 GROUP BY attack_range ORDER BY count DESC;
```

SELECT COUNT(*) AS num,role_main,AVG(hp_max) FROM heros WHERE hp_max >6000 GROUP BY role_main HAVING num >5 ORDER BY num DESC;
+-----+-----------+-------------+
| num | role_main | AVG(hp_max) |
+-----+-----------+-------------+
| 17 | 战士 | 7028 |
| 10 | 坦克 | 8312.4 |
| 6 | 法师 | 6417 |
+-----+-----------+-------------+
3 rows in set (0.02 sec)

# MariaDB:

1. SELECT role_main,AVG(hp_max) as avg_hp, COUNT(*) AS count FROM heros WHERE hp_max > 6000 GROUP BY role_main HAVING count > 5 ORDER BY count DESC;

2. SELECT COUNT(*) as num, ROUND(AVG(hp_max+mp_max),2), ROUND( MAX(hp_max+mp_max), 2), ROUND(MIN(hp_max+mp_max) ,2) FROM heros WHERE (hp_max + mp_max) > 7000 GROUP BY attack_range ORDER BY num DESC;