Mysql中根据子表的分组后的最大统计数获取主表中的信息

最新推荐文章于 2023-02-03 16:41:18 发布

原创最新推荐文章于 2023-02-03 16:41:18 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，旨在获取发帖数量最多的学生的所有信息。通过对帖子表进行分组并计算每个学生的发帖数量，找出最多的发帖数对应的学生ID，最后利用此ID查询学生表获取详细信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

连接mysql ：mysql -h主机地址 -u用户名 -p密码
修改密码：mysqladmin -u用户名 -p旧密码 password 新密码

显示当前的数据库：select database();
显示数据库版本：select version();
获取当前时间： select now();

...................还有简单的数据库操作不做例举

下面尝试一些数据库中相对比较复杂的查询语句。

案例1：我想获取某一个学生的全部信息，该学生的发帖数量最多。学生与帖子是俩个表，帖子关联学生的主键stu_id;

俩张表中的内容如下：

mysql> select pp_id,pp_title,stu_id from tb_postpage;
+-------+-----------+--------+
| pp_id | pp_title | stu_id |
+-------+-----------+--------+
|     1 | sdfasf    |      1 |
|     2 | 一起啊    |      1 |
|     3 | sfsdg     |      1 |
|     4 | jiayia    |      2 |
|     6 | dasfasf   |      2 |
|     7 | fasdfasdf |      3 |
|     8 |           |      3 |
+-------+-----------+--------+
7 rows in set

mysql> select stu_id,stu_count,stu_password from tb_student;
+--------+-----------+--------------+
| stu_id | stu_count | stu_password |
+--------+-----------+--------------+
|      1 | 123456    | 123456       |
|      2 | 140950105 | 051315       |
|      3 | 13145     | pangjiafnei |
+--------+-----------+--------------+

1.先要对tb_postpage做一个分组。

select * from tb_postpage group by stu_id;

   select pp_id,pp_title,stu_id from tb_postpage group by stu_id;
+-------+-----------+--------+
| pp_id | pp_title | stu_id |
+-------+-----------+--------+
|     1 | sdfasf    |      1 |
|     4 | jiayia    |      2 |
|     7 | fasdfasdf |      3 |
+-------+-----------+--------+
3 rows in set

2.获得每组分组之后的计数以及stu_id

mysql> select stu_id,count(*) as count from tb_postpage group by stu_id;
+--------+-------+
| stu_id | count |
+--------+-------+
|      1 |     3 |
|      2 |     2 |
|      3 |     2 |
+--------+-------+
3 rows in set

3.获取count中的最大值

mysql> select stu_id,max(count) from (select stu_id,count(*) as count from tb_postpage group by stu_id) aa;
+--------+------------+
| stu_id | max(count) |
+--------+------------+
| 1 | 3 |
+--------+------------+
1 row in set【注意，这里要为表起一个别名】

4.获取该表中的stu_id;

mysql> select stu_id from (select stu_id,max(count) from (select stu_id,count(*) as count from tb_postpage group by stu_id) aa) bb;
+--------+
| stu_id |
+--------+
| 1 |
+--------+
1 row in set

5.通过该id获取学生的全部信息

mysql> select stu_id,stu_count,stu_password from tb_student where stu_id = (select stu_id from (select stu_id,max(count) from (select stu_id,count(*) as count from tb_postpage group by stu_id) aa) bb);
+--------+-----------+--------------+
| stu_id | stu_count | stu_password |
+--------+-----------+--------------+
| 1 | 123456 | 123456 |
+--------+-----------+--------------+
1 row in set