关于mysql的操作，各大博主都贡献了特别多的笔记，在这里我们只谈经验。
背景：有如下一张student表和一张course表
mysql> select * from student;+----+----------+------+-------+------------+---------------------+--------+| id | name| age | score | birthday | insert_time | course |+----+----------+------+-------+------------+---------------------+--------+| 1 | zhangya | 15 | 98.0 | -01-27 | -08-10 16:30:57 | 2|| 2 | alice | 14 | 60.0 | -08-08 | -08-10 13:28:42 | 1|| 3 | zhangsan | 17 | 89.0 | -11-30 | -08-10 14:12:58 | 3|| 4 | lisi| 15 | 70.0 | -11-05 | -08-10 14:07:53 | 2|| 5 | wangwu | 15 | 90.0 | -05-18 | -08-10 11:58:23 | 1|| 6 | wangwu | 15 | 98.0 | -05-18 | -08-10 21:27:51 | 2|| 7 | wangwu | 15 | 70.0 | -05-18 | -08-10 21:27:51 | 3|| 8 | lisi| 15 | 100.0 | -11-05 | -08-10 14:07:53 | 3|| 9 | lisi| 15 | 50.0 | -11-05 | -08-10 14:07:53 | 1|| 10 | zhangsan | 18 | 89.0 | -05-18 | -08-10 14:03:26 | 1|| 11 | zhangsan | 15 | 60.0 | -05-18 | -08-10 21:27:51 | 2|| 12 | alice | 14 | 49.0 | -08-08 | -08-10 14:08:43 | 2|| 13 | alice | 14 | 99.0 | -08-08 | -08-10 14:08:43 | 3|| 14 | zhangya | 15 | 92.0 | -01-27 | -08-10 16:28:00 | 3|| 15 | zhangya | 15 | 85.0 | -01-27 | -08-10 16:28:03 | 1|+----+----------+------+-------+------------+---------------------+--------+15 rows in set (0.00 sec)
mysql> select * from course;+----+-------------+| id | course_name |+----+-------------+| 1 | 语文 || 2 | 数学 || 3 | 英语 || 4 | 物理 |+----+-------------+4 rows in set (0.00 sec)
问题一：单表查询，要求列出每位同学的成绩最大值、课程名，包括name、max(score)、course
错误1：select name,max(score),course from student group by name;
max(score)取值正确，但course的值不对
mysql> select name,max(score),course from student group by name;+----------+------------+--------+| name| max(score) | course |+----------+------------+--------+| alice | 99.0 | 1|| lisi|100.0 | 2|| wangwu | 98.0 | 1|| zhangsan | 89.0 | 3|| zhangya | 98.0 | 2|+----------+------------+--------+5 rows in set (0.00 sec)
错误2：select name,max(score),course from student group by name order by score desc;
max(score)取值正确，但course的值不对
mysql> select name,max(score),course from student group by name order by score desc;+----------+------------+--------+| name| max(score) | course |+----------+------------+--------+| zhangya | 98.0 | 2|| wangwu | 98.0 | 1|| zhangsan | 89.0 | 3|| lisi|100.0 | 2|| alice | 99.0 | 1|+----------+------------+--------+5 rows in set (0.00 sec)
正确答案：select s.name,max(s.score),s.course from (select * from student order by score desc) s group by s.name;
首先对score进行降序排列，再对排完序的表进行group by分组查询，这样就可以得到正确的结果了
mysql> select s.name,max(s.score),s.course from (select * from student order by score desc) s group by s.name;+----------+--------------+--------+| name| max(s.score) | course |+----------+--------------+--------+| alice | 99.0 | 3|| lisi| 100.0 | 3|| wangwu | 98.0 | 2|| zhangsan | 89.0 | 1|| zhangya | 98.0 | 2|+----------+--------------+--------+5 rows in set (0.00 sec)
问题二：关联查询，要求列出每位同学的成绩最大值、课程名，包括name、max(score)、course、course_name
错误一：select s.name,max(s.score),s.course,c.course_name from (select * from student order by score desc) s inner join course c on s.course=c.id group by s.name;
max(score)取值正确，但course的值不对，course_name不对
mysql> select s.name,max(s.score),s.course,c.course_name from (select * from student order by score desc) s inner join course c on s.course=c.id group by s.name;+----------+--------------+--------+-------------+| name| max(s.score) | course | course_name |+----------+--------------+--------+-------------+| alice | 99.0 | 1| 语文 || lisi| 100.0 | 1| 语文 || wangwu | 98.0 | 1| 语文 || zhangsan | 89.0 | 1| 语文 || zhangya | 98.0 | 1| 语文 |+----------+--------------+--------+-------------+5 rows in set (0.00 sec)
正确答案：select * from (select s.name,max(s.score),s.course from (select * from student order by score desc) s group by s.name) n inner join course c on n.course =c.id;
首先先把单表查询的正确结果当成一个表，再去把结果表和其他关联表结合，进行关联查询（inner join）。
mysql> select * from (select s.name,max(s.score),s.course from (select * from student order by score desc) s group by s.name) n inner join course c on n.course =c.id;+----------+--------------+--------+----+-------------+| name| max(s.score) | course | id | course_name |+----------+--------------+--------+----+-------------+| zhangsan | 89.0 | 1| 1 | 语文 || wangwu | 98.0 | 2| 2 | 数学 || zhangya | 98.0 | 2| 2 | 数学 || alice | 99.0 | 3| 3 | 英语 || lisi| 100.0 | 3| 3 | 英语 |+----------+--------------+--------+----+-------------+5 rows in set (0.00 sec)
以上就是我自己练习时遇到的坑，希望对你有帮助
