SQL基础（一八）--- 数据分组与聚合函数

最新推荐文章于 2024-04-18 10:04:10 发布

风凌乱雨纷飞

最新推荐文章于 2024-04-18 10:04:10 发布

阅读量554

点赞数

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/icer_wei/article/details/43529747

版权

数据库专栏收录该内容

23 篇文章 0 订阅

订阅专栏

查询各个年龄段的人数：

mysql> select fage, count(*) from t_employee
    -> group by fage;
+------+----------+
| fage | count(*) |
+------+----------+
|   22 |        1 |
|   23 |        2 |
|   25 |        2 |
|   27 |        1 |
|   28 |        3 |
+------+----------+
5 rows in set

查询每一分公司，各个年龄段的人数：

mysql> select fsubcompany,fage,count(*) from t_employee
    -> group by fsubcompany,fage;
+-------------+------+----------+
| fsubcompany | fage | count(*) |
+-------------+------+----------+
| Beijing     |   23 |        2 |
| Beijing     |   25 |        2 |
| Beijing     |   28 |        2 |
| ShenZhen    |   22 |        1 |
| ShenZhen    |   27 |        1 |
| ShenZhen    |   28 |        1 |
+-------------+------+----------+

查询每个分公司每个月需要向员工支付工资总额：

mysql> select fsubcompany,sum(fsalary) from t_employee
    -> group by fsubcompany;
+-------------+--------------+
| fsubcompany | sum(fsalary) |
+-------------+--------------+
| Beijing     | 30801.24     |
| ShenZhen    | 6300.80      |
+-------------+--------------+
2 rows in set

查询每个部门每个月需要向员工支付工资总额：

mysql> select fdepartment,sum(fsalary) from t_employee
    -> group by fdepartment;
+---------------+--------------+
| fdepartment   | sum(fsalary) |
+---------------+--------------+
| Development   | 10600.80     |
| HumanResource | 7401.24      |
| InfoTech      | 6700.00      |
| Sales         | 12400.00     |
+---------------+--------------+
4 rows in set

查询每个部门员工年龄的最大值和最小值：

mysql> select fdepartment,min(fage),max(fage) from t_employee
    -> group by fdepartment;
+---------------+-----------+-----------+
| fdepartment   | min(fage) | max(fage) |
+---------------+-----------+-----------+
| Development   |        25 |        28 |
| HumanResource |        23 |        25 |
| InfoTech      |        27 |        28 |
| Sales         |        22 |        28 |
+---------------+-----------+-----------+
4 rows in set