启蒙自助建站,标书制作员这工作好吗,手机无货源网店怎么开,做搜索引擎优化的企业1.GROUP BY GROUP BY 函数就是 SQL 中用来实现分组的函数#xff0c;其用于结合聚合函数#xff0c;能根据给定数据列的每个成员对查询结果进行分组统计#xff0c;最终得到一个分组汇总表。
mysql SELECT country, COUNT(country) AS teacher_count- FROM teacher…1.GROUP BY GROUP BY 函数就是 SQL 中用来实现分组的函数其用于结合聚合函数能根据给定数据列的每个成员对查询结果进行分组统计最终得到一个分组汇总表。
mysql SELECT country, COUNT(country) AS teacher_count- FROM teachers- GROUP BY country- ORDER BY teacher_count, country;
------------------------
| country | teacher_count |
------------------------
| JP | 1 |
| UK | 1 |
| USA | 1 |
| CN | 2 |
------------------------
4 rows in set (0.04 sec)HAVING 子句 我们在使用 WHERE 条件子句时会发现其不能与聚合函数联合使用为解决这一点SQL 中提供了 HAVING 子句。在使用时 HAVING 子句经常与 GROUP BY 联合使用HAVING 子句就是对分组统计函数进行过滤的子句。
mysql SELECT T.name, IFNULL(SUM(C.student_count),0) AS student_count- FROM courses C - RIGHT JOIN teachers T ON C.teacher_id T.id- GROUP BY T.id- HAVING student_count 3000- ORDER BY student_count, name;
---------------------------------
| name | student_count |
---------------------------------
| Linghu Chong | 0 |
| Northern Beggar | 0 |
| Southern Emperor | 1520 |
---------------------------------
3 rows in set (0.02 sec)例题 筛选出同一国家的教师平均年龄大于所有教师平均年龄的国家并获取这些国家的所有教师信息 拆分
.先选出教师平均年龄大于所有教师平均年龄的国家
SELECT country from teachers
group by country
having avg(age) (SELECT avg(age) from teachers).拿着这些国家去teachers表里找出教师信息
SELECT * from teachers
where country in(
SELECT country from teachers
group by country
having avg(age) (SELECT avg(age) from teachers)
);内联视图子查询 现需要查询国籍为美国USA且年龄最大的教师请使用内联视图子查询实现。
本题将从教师表中查询到的美国教师作为内联表再使用 WHERE 子句进行查询操作。
SELECT *
FROM (SELECT *FROM teachersWHERE country USA
) T
WHERE age (SELECT MAX(age)FROM teachersWHERE country USA
);
