导出-查询方法

/**
     * 组合条件 + 模糊查询 + 学生信息体测成绩 郑晓东 2017年12月16日08点47分
     * 优化逻辑和日志--宋学孟-2017年12月28日
     * @param institutionId 学院ID
     * @param professionId 专业ID
     * @param grade 年级
     * @param sex 性别
     * @param ispass 是否及格
     * @param strLike 模糊查询内容（学号、姓名）
     * @return
     */
    @Override
    public List
 
   selectStuPhysicalTotalByView(String institutionId, String professionId, String grade, String sex, String ispass, String strLike) {
        if (grade.contains("级")) {
            grade = grade.substring(0, grade.indexOf("级"));
        }
        List
  
    stuProjectScoreModelList = totalScoreDao.selectStuPhysicalTotalByView(institutionId.trim(),professionId.trim(),grade.trim(),sex.trim(),ispass.trim(),strLike.trim()); // Map
   
     singlescoreInfoMap = new HashMap<>(); //验证是否有体测记录，没有直接返回null；否则继续执行 if (CollectionUtils.isEmpty(stuProjectScoreModelList)) { logger.error("---根据条件查的学生信息和总分相关list为空---"); return null; } List
    
      totalScoreEntities = new ArrayList<>(); for (StuProjectScoreModel stuProjectScoreModel : stuProjectScoreModelList) { TotalScoreEntity totalScoreEntity = new TotalScoreEntity(); totalScoreEntity.setStudentId(stuProjectScoreModel.getStudentId()); totalScoreEntity.setSchoolYear(stuProjectScoreModel.getSchoolYear()); totalScoreEntities.add(totalScoreEntity); } List
     
       totalScoreModels = totalScoreDao.selectStuPhysicalToTotalModelByStuYearList(totalScoreEntities); Map
      
       > map = new HashMap<>(); for (TotalScoreModel totalScoreModel:totalScoreModels) { map.put(totalScoreModel.getStudentId()+totalScoreModel.getSchoolYear(),totalScoreModel.getSingleProjectScoreEntityList()); } for (StuProjectScoreModel stuProjectScoreModel : stuProjectScoreModelList) { String mapKey=stuProjectScoreModel.getStudentId()+stuProjectScoreModel.getSchoolYear(); if(map.containsKey(mapKey)){ List
       
         singleProjectScoreEntityList=map.get(mapKey); //验证是否有单项体测成绩记录 没有则进行下一个循环，否则继续执行 if (CollectionUtils.isEmpty(singleProjectScoreEntityList)) { continue; } stuProjectScoreModel=this.translateSinlgeProject(stuProjectScoreModel,singleProjectScoreEntityList); } } return stuProjectScoreModelList; } /** * 组合条件 + 模糊查询 +分页查询 学生信息体测成绩 郑晓东 2017年12月16日08点47分 * @param institutionId 学院ID * @param professionId 专业ID * @param grade 年级 * @param sex 性别 * @param ispass 是否及格 * @param strLike 模糊查询内容（学号、姓名） * @param page 页码 * @param pageSize 页大小 * @return */ @Override public PageInfo
        
          selectStuPhysicalTotalPageInfo(String institutionId, String professionId, String grade, String sex, String ispass, String strLike,int page,int pageSize){ PageHelper.startPage(page,pageSize); return new PageInfo<>(selectStuPhysicalTotalByView(institutionId.trim(),professionId.trim(),grade.trim(),sex.trim(),ispass.trim(),strLike.trim())); } private StuProjectScoreModel translateSinlgeProject(StuProjectScoreModel stuProjectScoreModel, List
         
           singleProjectScoreEntityList){ for (SingleProjectScoreEntity singleProjectScoreEntity : singleProjectScoreEntityList) { if (singleProjectScoreEntity == null) { continue; } Double result = singleProjectScoreEntity.getResult()!=null?singleProjectScoreEntity.getResult():0; Double score = singleProjectScoreEntity.getScore()!=null?singleProjectScoreEntity.getScore():0; switch (singleProjectScoreEntity.getProjectId().trim()){ case "001": //身高 stuProjectScoreModel.setHight(result); break; case "002": //体重 stuProjectScoreModel.setWeight(result); break; case "003": //深呼吸 stuProjectScoreModel.setLungCapacity(result); stuProjectScoreModel.setLungCapacityMark(score); break; case "004": //50米跑 stuProjectScoreModel.setFiftyMeters(result); stuProjectScoreModel.setFiftyMetersMark(score); break; case "005": //立定跳 stuProjectScoreModel.setStandingJump(result); stuProjectScoreModel.setStandingJumpMark(score); break; case "006": //坐位体前屈 stuProjectScoreModel.setSeatBodyBends(result); stuProjectScoreModel.setSeatBodyBendsMark(score); break; case "007": //800米耐力跑 stuProjectScoreModel.setEightMeters(DataTypeConvert.covertDouble2String(result)); stuProjectScoreModel.setEightMetersMark(score); break; case "008": //1000米耐力跑 stuProjectScoreModel.setTenMeters(DataTypeConvert.covertDouble2String(result)); stuProjectScoreModel.setTenMetersMark(score); break; case "009": //仰卧起坐 stuProjectScoreModel.setSitUps(result); stuProjectScoreModel.setSitUpsMark(score); break; case "010": //引体向上 stuProjectScoreModel.setPullUps(result); stuProjectScoreModel.setPullUpsMark(score); break; case "011": //体重指数 stuProjectScoreModel.setBmi(result); stuProjectScoreModel.setBmiMark(score); break; default: break; } } return stuProjectScoreModel; }
         
        
       
      
     
    
   
  
 

 
    
        SELECT
            DISTINCT
            vstu.institution_name as institutionName,
            vstu.major_name as majorName,
            vstu.class_code as classCode,
            vstu.class_name as className,
            vstu.code as code,
            vstu.name as name,
            vstu.nation as nation,
            vstu.sex as sex,
            vstu.grade as grade,
            tts.student_id as studentId,
            tts.school_year as schoolYear,
            tts.total_score as totalScore,
            tts.id as totalScoreId,
            tts.passornot as passornot
        FROM t_total_score tts,v_student_class_major_in vstu
        where tts.student_id=vstu.id
        and tts.is_delete = 0
        
            AND vstu.institution_id=#{institutionId}
        
        
            AND vstu.profession_id=#{professionId}
        
        
            AND vstu.grade=#{grade}
        

        
            AND vstu.sex=#{sex}
        

        
            AND tts.passornot = #{ispass}
        

        
            AND (
            vstu.name LIKE concat('%',#{strLike},'%')
            OR
            vstu.code LIKE concat('%',#{strLike},'%')
            )
        
    
    
 
    
        
            Select
                DISTINCT
                result.student_id,
                result.school_year,
                result.project_id,
                result.result,
                result.score,
                result.degree
            FROM
            
                (
                    SELECT * FROM t_single_project_score
                    WHERE
                        is_delete =0
                    AND student_id = #{item.studentId}
                    AND school_Year = #{item.schoolYear}
                )
            
            result
        
    

1.主要是一个查询的方法
根据 studentIdList和schoolYearList查询对应学生的总成绩或者各项成绩的思路：
第一种：使用union关键字，多少学生就执行多少次sql
第二种：传两个list到sql中，随意拼接查到相应的数据后，再对比判断，取出符合条件的数据

2.思考：第二种应该更好些（导入的方法中使用到这种）
其他考虑：？？？？？

3.导出逻辑：
    	（1）如果参数条件grade中包含“级”，如2017级，则截取2017
		（2）根据条件.trim()查询数据库总分表+学生相关的视图，得list
		（3）如果查询结果list空，则返回null+打印日志
		（4）循环拿得list中总分相关的studentId和学年放到总分的entity中存储
		（5）根据（4）中的总分实体list中的学生id+学年，查询单项成绩表（使用union--sql很长--为了是学生id和学年对应上），返回总分相关+相应的单项list组成的实体model的list
		（6）声明map，上述model的list循环中，key：studentId-学年，value：model.get 单表list（一个学生一个学年对应的所有单项list）
		（7）循环查询学生，组合其中的key（studentID+学年），如果map中包含key，就从map中拿到相应的key对应的list
		（8）如果list是空，continue下一个；不为空，把表结构的数据转为显示结构的数据