举个例子：
mysql的表结构如下：
班级表（tclass）: id ,name
课程表（tcourse）: id,name
学生表（tstudent）: id,code,name,classid
成绩表（tscore）: id,studentid,courseid,score
1、查询出各科成绩前三的学生
SELECT s1.courseid, s1.studentid, s1.score FROM tscore s1 LEFT JOIN ( SELECT DISTINCT courseid, score FROM tscore ) s2 ON s1.courseid = s2.courseid AND s1.score < s2.score GROUP BY s1.courseid, s1.studentid, s1.score HAVING COUNT(*)< 3 ORDER BY s1.courseid, s1.score DESC;
sql如上，可以查询各个课程前三的记录
首先要理解一下下面这句sql
SELECT s1.courseid, s1.studentid, s1.score FROM tscore s1 LEFT JOIN ( SELECT DISTINCT courseid, score FROM tscore ) s2 ON s1.courseid = s2.courseid AND s1.score < s2.score;
以成绩表为主表 左连接 SELECT DISTINCT courseid, score FROM tscore查出来的各科成绩去重记录表（不去重也可以，最好去重减少从表的数据量），连接条件是主表和从表的课程id相等，并且 主表的分数小于从表分数，
这样得到的结果是：每科成绩最高的数据只有一条（主表本身有一条数据，从表没有符合的，因为左连接，所以还是有一条主表数据），剩下的每科的各个分数数据条数跟处于第几位有关（主表本身一条，从表分数比主表分数高的都符合，多少条符合就有多少条数据），可以加排序看一下结果，如下：
SELECT s1.courseid, s1.studentid, s1.score, s2.score FROM tscore s1 LEFT JOIN ( SELECT DISTINCT courseid, score FROM tscore ) s2 ON s1.courseid = s2.courseid AND s1.score < s2.score ORDER BY s1.courseid, s1.score DESC;
除了主表最高分是第一，其他的有n条数据就是第n+1高分
然后再分组，以及通过having筛选出前3的分数
GROUP BY
s1.courseid,
s1.studentid,
s1.score
HAVING
COUNT(*)< 3
之后如果再需要学生编号、学生名字、课程名字，则再通过内联学生表和课程表拿到对应的数据就可以了。
2、查询总成绩年级前五的学生
其实跟上面的思路是一样的，只不过多了求和
SELECT zt1.studentid, zt1.zscore FROM ( SELECT studentid, SUM( score ) AS zscore FROM tscore GROUP BY studentid ) AS zt1 LEFT JOIN ( SELECT DISTINCT SUM( score ) AS zscore FROM tscore GROUP BY studentid ) AS zt2 ON zt1.zscore < zt2.zscore GROUP BY zt1.studentid, zt1.zscore HAVING COUNT(*)< 5 ORDER BY zt1.zscore DESC;
3、查询各班级总成绩前三的学生
查询前几的思路都跟第1的思路一样
SELECT zt1.classid, zt1.studentid, zt1.zscore FROM ( SELECT studentid, SUM( score ) AS zscore, st.classid, st.`code`, st.`name` FROM tscore INNER JOIN tstudent st ON studentid = st.id GROUP BY studentid ) zt1 LEFT JOIN ( SELECT DISTINCT SUM( score ) AS zscore, st.classid FROM tscore INNER JOIN tstudent st ON studentid = st.id GROUP BY studentid, st.classid ) zt2 ON zt1.classid = zt2.classid AND zt1.zscore < zt2.zscore GROUP BY zt1.studentid, zt1.zscore, zt1.classid HAVING count(*)< 3 ORDER BY zt1.classid, zt1.zscore DESC;
4、提供上述问题的创表和创数据sql
CREATE TABLE `tclass` ( `id` int NOT NULL, `name` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; CREATE TABLE `tcourse` ( `id` int NOT NULL, `name` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; CREATE TABLE `tstudent` ( `id` int NOT NULL, `code` varchar(10) NOT NULL, `name` varchar(10) NOT NULL, `classid` int NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; CREATE TABLE `tscore` ( `id` int NOT NULL, `studentid` int NOT NULL, `courseid` int NOT NULL, `score` int NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
INSERT INTO `tclass` (`id`, `name`) VALUES (1001, '班级6-1'),(1002, '班级6-2'),(1003, '班级6-3'); INSERT INTO `tcourse` (`id`, `name`) VALUES (2001, '语文'),(2002, '数学'),(2003, '英语'); INSERT INTO `tstudent` (`id`, `code`, `name`, `classid`) VALUES (3001, '20230001', '张一', 1001),(3002, '20230002', '李二', 1001),(3003, '20230003', '王三', 1001),(3004, '20230004', '赵四', 1001), (3005, '20230005', '刘五', 1001),(3006, '20230006', '唐六', 1001),(3007, '20230007', '宋七', 1001),(3008, '20230008', '元八', 1001),(3009, '20230009', '明九', 1001),(3010, '20230010', '清十', 1001), (3011, '20230011', '张进', 1002),(3012, '20230012', '李说', 1002),(3013, '20230013', '王时', 1002),(3014, '20230014', '赵舒', 1002),(3015, '20230015', '刘好', 1002),(3016, '20230016', '唐阿', 1002), (3017, '20230017', '宋公', 1002),(3018, '20230018', '元感', 1002),(3019, '20230019', '明更', 1002),(3020, '20230020', '清案', 1002),(3021, '20230021', '张互动', 1003),(3022, '20230022', '李积分', 1003), (3023, '20230023', '王哇', 1003),(3024, '20230024', '赵发', 1003),(3025, '20230025', '刘缺', 1003),(3026, '20230026', '唐测', 1003),(3027, '20230027', '宋那', 1003),(3028, '20230028', '元真', 1003), (3029, '20230029', '明不', 1003),(3030, '20230030', '清阿文', 1003); INSERT INTO `tscore` (`id`, `studentid`, `courseid`, `score`) VALUES (4001, 3001, 2001, 80),(4002, 3001, 2002, 81),(4003, 3001, 2003, 71),(4004, 3002, 2001, 76),(4005, 3002, 2002, 87),
(4006, 3002, 2003, 41),(4007, 3003, 2001, 89),(4008, 3003, 2002, 65),(4009, 3003, 2003, 90),(4010, 3004, 2001, 65),(4011, 3004, 2002, 58),(4012, 3004, 2003, 78),(4013, 3005, 2001, 67),
(4014, 3005, 2002, 54),(4015, 3005, 2003, 79),(4016, 3006, 2001, 87),(4017, 3006, 2002, 90),(4018, 3006, 2003, 88),(4019, 3007, 2001, 66),(4020, 3007, 2002, 73),(4021, 3007, 2003, 77),
(4022, 3008, 2001, 27),(4023, 3008, 2002, 76),(4024, 3008, 2003, 60),(4025, 3009, 2001, 76),(4026, 3009, 2002, 77),(4027, 3009, 2003, 83),(4028, 3010, 2001, 56),(4029, 3010, 2002, 68),
(4030, 3010, 2003, 83),(4031, 3011, 2001, 70),(4032, 3011, 2002, 61),(4033, 3011, 2003, 91),(4034, 3012, 2001, 74),(4035, 3012, 2002, 83),(4036, 3012, 2003, 71),(4037, 3013, 2001, 79),
(4038, 3013, 2002, 85),(4039, 3013, 2003, 60),(4040, 3014, 2001, 35),(4041, 3014, 2002, 68),(4042, 3014, 2003, 58),(4043, 3015, 2001, 77),(4044, 3015, 2002, 64),(4045, 3015, 2003, 89),
(4046, 3016, 2001, 47),(4047, 3016, 2002, 70),(4048, 3016, 2003, 98),(4049, 3017, 2001, 76),(4050, 3017, 2002, 53),(4051, 3017, 2003, 87),(4052, 3018, 2001, 87),(4053, 3018, 2002, 96),
(4054, 3018, 2003, 90),(4055, 3019, 2001, 66),(4056, 3019, 2002, 67),(4057, 3019, 2003, 83),(4058, 3020, 2001, 66),(4059, 3020, 2002, 58),(4060, 3020, 2003, 73),(4061, 3021, 2001, 70),
(4062, 3021, 2002, 71),(4063, 3021, 2003, 81),(4064, 3022, 2001, 76),(4065, 3022, 2002, 82),(4066, 3022, 2003, 75),(4067, 3023, 2001, 77),(4068, 3023, 2002, 87),(4069, 3023, 2003, 67),
(4070, 3024, 2001, 38),(4071, 3024, 2002, 67),(4072, 3024, 2003, 59),(4073, 3025, 2001, 87),(4074, 3025, 2002, 84),(4075, 3025, 2003, 79),(4076, 3026, 2001, 57),(4077, 3026, 2002, 60),
(4078, 3026, 2003, 78),(4079, 3027, 2001, 86),(4080, 3027, 2002, 63),(4081, 3027, 2003, 67),(4082, 3028, 2001, 77),(4083, 3028, 2002, 76),(4084, 3028, 2003, 90),(4085, 3029, 2001, 96),
(4086, 3029, 2002, 87),(4087, 3029, 2003, 83),(4088, 3030, 2001, 66),(4089, 3030, 2002, 68),(4090, 3030, 2003, 78);
5、记录一下mysql替换字符串
详情可查看：https://www.cnblogs.com/zlf2000/p/16741839.html
a、使用REPLACE()函数
REPLACE()函数用新的子字符串替换字符串中所有出现的子字符串。
注意：此函数执行区分大小写的替换。
REPLACE(string, from_string, new_string)
select REPLACE(`name`,'彭','清')as `name` from tstudent WHERE `name` like '彭%';
b、使用INSERT()函数
INSERT()函数在指定位置的字符串中插入一个字符串，并插入一定数量的字符。
INSERT(string, position, number, string2)
select INSERT(`name`,1,1,'清') as `name` from tstudent WHERE `name` like '彭%';
可以用在修改语句
UPDATE tstudent set `name`=REPLACE(`name`,'彭','清') WHERE `name` like '彭%'; UPDATE tstudent set `name`=INSERT(`name`,1,1,'清') WHERE `name` like '彭%';
