java多表查询成绩表面试题及答案

java多表查询成绩表面试题及答案学生表student：s_idVARCHAR(20)PRIMARYKEYCOMMENT'学生学号',s_nameVARCHAR(20)NOTNULLCOMMENT'学生姓名',s_classVARCHAR(20)COMMENT'所在班级'课程表course：c_idVARCHAR(20)PRIMARYKEYCOMMENT'课程编号',c_nameVARCHAR(20)NOTNULLCOMMENT'课程名称',t_idVARCHAR(20)NOTNULLCOMMENT'授课教师编号'教师表teacher：t_idVARCHAR(20)PRIMARYKEYCOMMENT'教师编号',t_nameVARCHAR(20)NOTNULLCOMMENT'教师姓名'成绩表score：s_idVARCHAR(20)COMMENT'学生学号',c_idVARCHAR(20)COMMENT'课程编号',s_scoreINTCOMMENT'考试成绩',PRIMARYKEY(s_id,c_id),FOREIGNKEY(s_id)REFERENCESstudent(s_id),FOREIGNKEY(c_id)REFERENCEScourse(c_id)问题1：请查询所有学生的姓名、所在班级、选修的课程名、对应考试成绩，要求未选修任何课程的学生信息也要展示。答案：SQL层面要使用student表左外连接score表，再左外连接course表，避免过滤掉未选课的学生，SQL语句如下：SELECTs.s_name,s.s_class,c.c_name,sc.s_scoreFROMstudentsLEFTJOINscorescONs.s_id=sc.s_idLEFTJOINcoursecONsc.c_id=c.c_id;Java层面处理：首先定义传输对象StudentScoreDTO，属性包含StringsName、StringsClass、StringcName、IntegersScore；如果使用MyBatis进行映射，在Mapper.xml中配置resultMap做字段映射即可，示例配置：<resultMapid="StudentScoreMap"type="com.example.dto.StudentScoreDTO"><resultproperty="sName"column="s_name"/><resultproperty="sClass"column="s_class"/><resultproperty="cName"column="c_name"/><resultproperty="sScore"column="s_score"/></resultMap><selectid="listAllStudentScore"resultMap="StudentScoreMap">上述SQL语句</select>如果需要按学生维度聚合数据，将同一个学生的多门课程成绩放到同一个对象的集合属性中，可以配置嵌套resultMap，实体类StudentVO新增List<CourseScore>courseScores属性，CourseScore包含cName和sScore属性，resultMap配置如下：<resultMapid="StudentCourseMap"type="com.example.vo.StudentVO"><idproperty="sId"column="s_id"/><resultproperty="sName"column="s_name"/><resultproperty="sClass"column="s_class"/><collectionproperty="courseScores"ofType="com.example.vo.CourseScore"><resultproperty="cName"column="c_name"/><resultproperty="sScore"column="s_score"/></collection></resultMap>注意这里必须加id标签指定student表的主键，否则MyBatis无法正确合并同一学生的多条成绩记录。问题2：请查询平均成绩大于等于60分的学生学号、姓名和平均成绩，结果按平均成绩降序排序。答案：SQL层面需要按学生分组，使用AVG聚合函数计算平均成绩，通过HAVING子句过滤分组后的结果，注意要排除成绩为null的无效记录，SQL如下：SELECTs.s_id,s.s_name,ROUND(AVG(sc.s_score),2)ASavg_scoreFROMstudentsINNERJOINscorescONs.s_id=sc.s_idWHEREsc.s_scoreISNOTNULLGROUPBYs.s_id,s.s_nameHAVINGAVG(sc.s_score)>=60ORDERBYavg_scoreDESC;Java层面注意：因为AVG函数返回的是BigDecimal类型，DTO定义avg_score属性时建议使用BigDecimal避免精度丢失，不要使用float或者double；如果使用JPA的话可以通过@Query注解写原生SQL或者JPQL，MyBatis直接在xml中配置对应resultMap即可，同时如果需要对平均成绩做后续运算，直接使用BigDecimal的API进行操作即可。常见错误点：很多开发者会把聚合结果的过滤条件写在WHERE子句中，WHERE是分组前过滤，HAVING是分组后过滤，平均成绩是分组后的聚合结果，必须放在HAVING中，否则会报语法错误。问题3：查询所有同学的学号、姓名、选课数、总成绩，未选课的同学选课数和总成绩显示为0。答案：SQL层面需要左连接成绩表，使用COUNT和SUM聚合函数，配合IFNULL（MySQL）或者COALESCE（通用SQL函数）处理null值，SQL如下：SELECTs.s_id,s.s_name,IFNULL(COUNT(sc.c_id),0)AScourse_count,IFNULL(SUM(sc.s_score),0)AStotal_scoreFROMstudentsLEFTJOINscorescONs.s_id=sc.s_idANDsc.s_scoreISNOTNULLGROUPBYs.s_id,s.s_name;注意这里判断成绩不为空的条件要放在ON子句中，不能放在WHERE子句中，否则左连接会变成内连接，过滤掉未选课的学生。Java层面如果需要导出该统计结果到Excel，可以直接把查询到的DTO集合传入EasyExcel或者POI工具类，不需要额外处理null值，因为SQL已经做了默认值处理，避免导出时出现空指针。问题4：请查询所有姓“张”的教师所教授的课程的学生选课成绩，要求显示教师姓名、课程名、学生姓名、班级、成绩，同时支持按教师姓名、课程名做模糊搜索。答案：SQL层面需要关联四张表，使用动态SQL拼接查询条件，基础语句如下：SELECTt.t_name,c.c_name,s.s_name,s.s_class,sc.s_scoreFROMteachertINNERJOINcoursecONt.t_id=c.t_idINNERJOINscorescONc.c_id=sc.c_idINNERJOINstudentsONsc.s_id=s.s_idWHEREt.t_nameLIKE'张%'<iftest="teacherName!=nullandteacherName!=''">ANDt.t_nameLIKECONCAT('%',#{teacherName},'%')</if><iftest="courseName!=nullandcourseName!=''">ANDc.c_nameLIKECONCAT('%',#{courseName},'%')</if>;Java层面使用MyBatis的动态SQL实现条件拼接，注意要做SQL注入防护，不要使用${}拼接参数，要使用#{}预编译处理；如果使用MyBatis-Plus，可以使用MPJLambdaWrapper（MyBatis-PlusJoin扩展）实现链式多表查询，示例代码：List<TeacherCourseScoreDTO>list=mpjLambdaMapper.selectJoinList(TeacherCourseScoreDTO.class,MPJLambdaWrapper.select(Teacher::getTName,Course::getCName,Student::getSName,Student::getSClass,Score::getSScore).leftJoin(Teacher.class,Teacher::getTId,Course::getTId).leftJoin(Course.class,Course::getCId,Score::getCId).leftJoin(Score.class,Score::getSId,Student::getSId).likeRight(Teacher::getTName,"张").like(StringUtils.isNotBlank(teacherName),Teacher::getTName,teacherName).like(StringUtils.isNotBlank(courseName),Course::getCName,courseName));这种方式不需要写XML，代码可维护性更高，适合复杂多条件的多表查询场景。问题5：请查询同时选修了“高等数学”和“大学物理”两门课程的学生姓名、班级，以及这两门课的各自成绩。答案：实现方式有两种，第一种是成绩表自连接，第二种是分组后统计符合条件的课程数，自连接方式查询效率更高，SQL如下：SELECTs.s_name,s.s_class,sc1.s_scoreASmath_score,sc2.s_scoreASphysics_scoreFROMstudentsINNERJOINscoresc1ONs.s_id=sc1.s_idINNERJOINcoursec1ONsc1.c_id=c1.c_idANDc1.c_name='高等数学'INNERJOINscoresc2ONs.s_id=sc2.s_idINNERJOINcoursec2ONsc2.c_id=c2.c_idANDc2.c_name='大学物理';分组实现方式：SELECTs.s_name,s.s_class,MAX(IF(c.c_name='高等数学',sc.s_score,NULL))ASmath_score,MAX(IF(c.c_name='大学物理',sc.s_score,NULL))ASphysics_scoreFROMstudentsINNERJOINscorescONs.s_id=sc.s_idINNERJOINcoursecONsc.c_id=c.c_idWHEREc.c_nameIN('高等数学','大学物理')GROUPBYs.s_id,s.s_nameHAVINGCOUNT(DISTINCTc.c_id)=2;Java层面注意：如果后续需要对比两门课的分差，可以在DTO中新增getScoreDifference方法，直接返回两门成绩的差值，不需要在业务层额外做计算。问题6：请对所有学生的“计算机基础”课程成绩做排名，要求相同分数排名并列，后续排名不跳过（比如两个90分都是第1名，88分就是第2名），同时支持分页查询。答案：SQL层面使用窗口函数DENSE_RANK实现，MySQL8.0及以上版本支持窗口函数，SQL如下：SELECTs.s_name,s.s_class,sc.s_score,DENSE_RANK()OVER(ORDERBYsc.s_scoreDESC)ASrank_numFROMstudentsINNERJOINscorescONs.s_id=sc.s_idINNERJOINcoursecONsc.c_id=c.c_idWHEREc.c_name='计算机基础'ORDERBYrank_numASCLIMIT#{offset},#{pageSize};如果需要相同分数排名并列且跳过后续排名（两个90分第1，88分第3），则使用RANK()函数，如果不需要并列排名，相同分数按学号排序分先后，使用ROW_NUMBER()函数。Java层面如果是低版本MySQL不支持窗口函数，可以在业务层处理排序和排名，示例代码：List<StudentRankVO>list=studentMapper.listComputerScore();intrank=1;for(inti=0;i<list.size();i++){if(i>0&&!list.get(i).getSScore().equals(list.get(i-1).getSScore())){rank=i+1;}list.get(i).setRankNum(rank);}分页可以用subList处理，或者分页插件处理后再做排名计算，注意分页后排名是当前页的排名还是全局排名，如果要全局排名必须先计算排名再分页。问题7：多表查询中常见的N+1问题是什么？在Java的持久层框架中怎么解决？答案：N+1问题指的是查询主表数据时执行了1次SQL，之后为了获取关联的从表数据，每一条主表记录都执行一次查询从表的SQL，总共执行1+N次SQL，数据量大时性能极差。MyBatis中解决方式有两种：第一种是使用嵌套结果映射，也就是写关联查询的SQL，一次查询出所有需要的主从表数据，通过resultMap的collection或者association标签映射，只执行1次SQL；第二种是使用延迟加载，配置lazyLoadingEnabled=true，只有用到关联属性的时候才会去查询从表，适合不需要每次都返回关联属性的场景。MyBatis-Plus中可以使用@TableName注解的autoResultMap属性配合@ResultMap、@One、@Many注解，或者使用MyBatis-PlusJoin插件直接做关联查询，避免N+1问题。JPA中解决方式是使用fetchjoin，在JPQL中指定关联属性fetch，一次查询出所有关联数据，或者使用@EntityGraph注解指定要加载的关联属性，避免懒加载触发N+1查询。问题8：现在有一个需求，需要统计每个班级的各科目