以下内容是本人自己写的，并没有找到此题的官方解答，如有更好的解题过程，请不吝赐教

详细问题描述

解答

第一问

完整答案

SELECT student.s_id,student.s_name,COUNT(score.c_id),IF(SUM(score.`s_score`) IS NULL,0,SUM(score.`s_score`))FROM student LEFT JOIN score ON student.`s_id` = score.`s_id` GROUP BY student.s_id

效果

第二问

完整答案

SELECT * FROM student JOIN score ON student.`s_id`=score.`s_id` JOIN course ON score.`c_id` = course.`c_id` WHERE (score.`c_id`,score.`s_score`) IN (SELECT c_id,MAX(s_score) AS s_score FROM score WHERE c_id IN (SELECT c_id FROM course WHERE t_id = (SELECT t_id FROM teacher WHERE t_name= "张三") ) GROUP BY c_id)

效果（有门课有两个最高成绩的学生）

第二问步骤描述

SELECT c_id,MAX(s_score) AS s_score FROM score WHERE c_id IN (SELECT c_id FROM course WHERE t_id = (SELECT t_id FROM teacher WHERE t_name= "张三") ) GROUP BY c_id

效果展示

步骤二（将所需要的信息表关联）

SELECT * FROM student JOIN score ON student.`s_id`=score.`s_id` JOIN course ON score.`c_id` = course.`c_id`

效果展示

步骤三（要注意in的字段）

WHERE (score.`c_id`,score.`s_score`) in (步驟一的結果)

帮助到你的话，点个赞，鼓励一下，欢迎加入我的置顶博客设置的技术交流群，不搞代课推广，让我们一起向诗靠拢。

附件sql表

/*SQLyog Ultimate v12.09 (64 bit)MySQL - 5.7.29-log : Database - review**********************************************************************//*!40101 SET NAMES utf8 */;/*!40101 SET SQL_MODE=''*/;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;CREATE DATABASE /*!32312 IF NOT EXISTS*/`review` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `review`;/*Table structure for table `course` */DROP TABLE IF EXISTS `course`;CREATE TABLE `course` (`c_id` int(10) unsigned NOT NULL AUTO_INCREMENT,`c_name` varchar(255) NOT NULL,`t_id` int(10) unsigned NOT NULL,PRIMARY KEY (`c_id`),KEY `t_id` (`t_id`),CONSTRAINT `course_ibfk_1` FOREIGN KEY (`t_id`) REFERENCES `teacher` (`t_id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;/*Data for the table `course` */insert into `course`(`c_id`,`c_name`,`t_id`) values (1,'语文',1),(2,'编译原理',2),(3,'Linux',3),(4,'数学',1);/*Table structure for table `score` */DROP TABLE IF EXISTS `score`;CREATE TABLE `score` (`s_id` int(10) unsigned NOT NULL,`c_id` int(10) unsigned NOT NULL,`s_score` int(10) unsigned NOT NULL,KEY `s_id` (`s_id`),KEY `c_id` (`c_id`),CONSTRAINT `score_ibfk_1` FOREIGN KEY (`s_id`) REFERENCES `student` (`s_id`),CONSTRAINT `score_ibfk_2` FOREIGN KEY (`c_id`) REFERENCES `course` (`c_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `score` */insert into `score`(`s_id`,`c_id`,`s_score`) values (1,1,80),(2,1,80),(3,1,60),(7,1,50),(1,2,81),(2,2,71),(3,2,61),(7,2,51),(1,4,82),(2,4,72),(3,4,62),(7,4,52);/*Table structure for table `student` */DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (`s_id` int(10) unsigned NOT NULL AUTO_INCREMENT,`s_name` varchar(255) NOT NULL,`s_birth` datetime NOT NULL,`s_sex` varchar(255) DEFAULT NULL,PRIMARY KEY (`s_id`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;/*Data for the table `student` */insert into `student`(`s_id`,`s_name`,`s_birth`,`s_sex`) values (1,'马云','-07-08 09:47:50','男'),(2,'孙传明','-07-08 09:48:08','男'),(3,'臧山松','-07-08 09:48:47','男'),(4,'贾小明','-07-08 09:49:02','男'),(5,'徐海露','-07-08 09:49:16','女'),(6,'尤路英','-07-08 09:49:36','女'),(7,'胡吃海塞','-07-08 09:49:56','男');/*Table structure for table `teacher` */DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` (`t_id` int(10) unsigned NOT NULL AUTO_INCREMENT,`t_name` varchar(255) NOT NULL,PRIMARY KEY (`t_id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;/*Data for the table `teacher` */insert into `teacher`(`t_id`,`t_name`) values (1,'张三'),(2,'李华'),(3,'牛宝丽');/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

sql面试题：问题1:查询每个同学的学生编号 学生姓名 选课总数...问题2：查询“张三”老师所授课程的学生中 成绩最高的学生信息...