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

绝地灬酷狼 2023-02-24 03:58 12阅读 0赞

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

# 详细问题描述 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MzQwNjAx_size_16_color_FFFFFF_t_70]

# 解答 #

## 第一问 ##

### 完整答案 ###

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

### 效果 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MzQwNjAx_size_16_color_FFFFFF_t_70 1]

## 第二问 ##

### 完整答案 ###

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
    	)

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

![在这里插入图片描述][20200708123520878.png]

## 第二问步骤描述 ##

*  步骤一（查找这个老师所带课程的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

效果展示

![在这里插入图片描述][20200708123615797.png]

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

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

效果展示  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MzQwNjAx_size_16_color_FFFFFF_t_70 2]

*  步骤三（要注意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,'马云','2020-07-08 09:47:50','男'),(2,'孙传明','2020-07-08 09:48:08','男'),(3,'臧山松','2020-07-08 09:48:47','男'),(4,'贾小明','2020-07-08 09:49:02','男'),(5,'徐海露','2020-07-08 09:49:16','女'),(6,'尤路英','2020-07-08 09:49:36','女'),(7,'胡吃海塞','2020-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 */;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MzQwNjAx_size_16_color_FFFFFF_t_70]: /images/20230209/e0c529e86207429d8ddcbc4a99bead96.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MzQwNjAx_size_16_color_FFFFFF_t_70 1]: /images/20230209/8cfd6d7ef4864d8d893bfed2afeabb55.png
[20200708123520878.png]: /images/20230209/bafef4173dc84cbb8b0e2ca1b60dda92.png
[20200708123615797.png]: /images/20230209/85d04c5910e14a208d44fe81e0e85cd8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MzQwNjAx_size_16_color_FFFFFF_t_70 2]: /images/20230209/a94ab5fcb90a4b3a934b512909352859.png