1：测试代码：Dapper集合Linq处理报表
传参数这块直接使用单个的参数了，嘻嘻
1 public async Task<TheMostUnitAndClassStudentDataShowokFormart> TheMostVolatileClassGetStudentExamInfo(string uni_id, int level, int gp_id, int gc_id, int ga_id) 2 { 3 // --AND M.ttm_date >= @sd AND M.ttm_date <= @ed 暂时注释，方便测试 4 var data = new TheMostUnitAndClassStudentDataShowokFormart 5 { 6 examNames = new List<string>(), 7 studentEveryExamTotalScore = new List<decimal[]>(), 8 studentName = new List<string>() 9 }; 10 var listdata = new List<TheMostUnitAndClassGroupStudentDataShowList>(); 11 if (!uni_id.IsNullOrEmptyStr())//校级调用的时候,求波动最大的班级 12 { 13 string sql = @"DROP temporary TABLE if EXISTS mytempdatatable01; 14 CREATE TEMPORARY table mytempdatatable01 15 SELECT tu.unit_session_uid,us.unit_session_sdate,us.unit_session_edate FROM unit tu 16 INNER JOIN unit_session us ON tu.uni_id=us.uni_id AND us.unit_session_type='1' 17 WHERE tu.uni_id=@uni_id AND tu.isdel=0 and us.isdel=0 LIMIT 1; 18 SET @sd='1990-01-01',@ed='1990-01-01'; 19 SET @sd=(SELECT unit_session_sdate FROM mytempdatatable01); 20 SET @ed=(SELECT unit_session_edate FROM mytempdatatable01); 21 22 SELECT distinct 23 U.ttm_id,U.ttm_name,U.uni_id ,U.stt_avgscore ,U.stt_id,U.class_uid 24 FROM( 25 SELECT distinct 26 M.ttm_id,M.ttm_name,D.class_uid,M.uni_id,SM.cou_id ,SM.stt_avgscore,TD.stt_id,C.class_level 27 FROM test_main M 28 LEFT JOIN test_detail D ON M.ttm_id=D.ttm_id 29 LEFT JOIN school_test_transcripts SM ON M.ttm_id=SM.ttm_id 30 LEFT JOIN school_test_transcripts_detail TD ON SM.stt_id=TD.stt_id 31 INNER JOIN class C ON D.class_uid=C.class_uid 32 WHERE 1=1 33 AND M.uni_id=@uni_id 34 AND SM.uni_id=@uni_id 35 AND M.isdel=0 AND D.isdel=0 AND TD.isdel=0 AND TD.score_type=0 36 -- AND M.ttm_date>=@sd AND M.ttm_date<=@ed 37 GROUP BY M.uni_id,M.ttm_id,M.ttm_name,TD.stt_id 38 )U 39 INNER JOIN unit_session S ON U.uni_id=S.uni_id AND S.unit_session_type=1 40 AND U.stt_avgscore IS NOT null 41 AND U.uni_id=@uni_id 42 WHERE U.class_level=@class_level 43 GROUP BY U.uni_id, U.ttm_id,U.ttm_name,U.stt_id; "; 44 var modelFirst = await ZRF_DapperHelper.QueryAsync<TheMostUnitAndClassGroupStudentTempInfo>(sql, new { uni_id = uni_id, class_level = level }); 45 if (modelFirst == null || !modelFirst.Any()) 46 return data; 47 48 var ttm_ids = modelFirst.Select(c => c.ttm_id).Distinct().ToList();//有多少次 ttm_id考试 49 50 var tempDic = new Dictionary<string, decimal>(); 51 var tempEveryStudentScoreInfoList = new List<GetStudentThisExamPerIDAndScore>(); 52 for (int m = 0; m < ttm_ids.Count; m++) 53 { 54 //每次考试有多少班级考试的次数，可能是同一个班级考试 55 var everyExamStudentCoreInfo = (await ZRF_DapperHelper.QueryAsync<GetStudentThisExamPerIDAndScore>(@" 56 SELECT 57 M.ttm_id,D.stt_id,D.per_id,D.sttd_score,P.per_name 58 FROM 59 school_test_transcripts M 60 LEFT join school_test_transcripts_detail D on M.stt_id=D.stt_id 61 INNER JOIN personnel P ON D.per_id=P.per_id AND P.isdel=0 62 WHERE M.isdel=0 AND D.isdel=0 AND M.ttm_id=@ttm_id GROUP BY D.stt_id,D.per_id,D.sttd_score", new { ttm_id = ttm_ids[m] })).ToList(); 63 if (everyExamStudentCoreInfo != null && everyExamStudentCoreInfo.Any()) 64 { 65 tempEveryStudentScoreInfoList.AddRange(everyExamStudentCoreInfo); 66 var examCout = everyExamStudentCoreInfo.Select(c => c.stt_id).Distinct().ToList();//其中考试 考了几次 67 var disticntStudentPer_Id = everyExamStudentCoreInfo.Select(c => c.per_id).Distinct().ToList();//这一批考试的所有学生去重 68 decimal getThisExamAvgScore = everyExamStudentCoreInfo.Select(c => c.sttd_score).Average();//这次考试的平均分 69 for (int s = 0; s < disticntStudentPer_Id.Count; s++)//求每一个学生的标准差 70 { 71 string thisStudent = disticntStudentPer_Id[s];//这个学生的per_id 72 //string studentName = everyExamStudentCoreInfo.FirstOrDefault(c => c.per_id == thisStudent).per_name; 73 //decimal totalScore = everyExamStudentCoreInfo.Where(c => c.per_id == thisStudent).Select(c => c.sttd_score).Sum();//这个学生的总分 74 decimal bzc = 0; 75 List<decimal> thisStudentScore = everyExamStudentCoreInfo.Where(c => c.per_id == thisStudent).Select(c => c.sttd_score).ToList(); 76 for (int studentScorecount = 0; studentScorecount < thisStudentScore.Count; studentScorecount++)//该考了几次考试 77 { 78 bzc += (thisStudentScore[studentScorecount] - getThisExamAvgScore) * (thisStudentScore[studentScorecount] - getThisExamAvgScore); 79 } 80 double tempbzf = double.Parse(bzc.ToString()) * 1.0 / examCout.Count; 81 82 bzc = decimal.Parse(Math.Sqrt(tempbzf).ToString("f2")); 83 // listper_id.Add($"{thisStudent}_{ttm_ids[m]}_{studentName}", bzc); 84 if (!tempDic.ContainsKey(thisStudent)) 85 { 86 tempDic.Add(thisStudent, bzc); 87 } 88 } 89 } 90 } 91 var dictinctDicStudent = tempDic.OrderByDescending(c => c.Value).Take(5).ToList();//不重复的5个学生 92 93 List<string> examName = new List<string>();//考试的名称 94 List<string> studentName = new List<string>();//学生名称 95 List<decimal[]> top5Score = new List<decimal[]>();//学生分数 96 97 foreach (var item in dictinctDicStudent) 98 { 99 string per_id = item.Key; 100 string per_name = tempEveryStudentScoreInfoList.FirstOrDefault(c => c.per_id == per_id).per_name; 101 studentName.Add(per_name); 102 103 List<decimal> everyExamScoreForStudent = new List<decimal>(); 104 for (int i = 0; i < ttm_ids.Count; i++) 105 { 106 string exam_Name = modelFirst.FirstOrDefault(c => c.ttm_id == ttm_ids[i]).ttm_name; 107 if (!examName.Contains(exam_Name)) 108 { 109 examName.Add(exam_Name); 110 } 111 everyExamScoreForStudent.Add(tempEveryStudentScoreInfoList.Where(c => c.per_id == per_id && c.ttm_id == ttm_ids[i]).Select(c => c.sttd_score).Sum()); 112 } 113 top5Score.Add(everyExamScoreForStudent.ToArray()); 114 } 115 116 #region MyRegion 存在重复的逻辑 117 //var diclist = listper_id.OrderByDescending(c => c.Value).Take(5).ToList(); 118 119 //List<string> top5Studentid = new List<string>();//学生id 120 //List<decimal[]> top5Score = new List<decimal[]>();//学生分数 121 //List<string> examName = new List<string>(); 122 ////5个学生 123 //diclist.Select(c => c.Key).ToList().ForEach(c => 124 //{ 125 // string ttm_id = c.Split("_")[1]; 126 // string per_id = c.Split("_")[0]; 127 // string _examName = modelFirst.FirstOrDefault(c => c.ttm_id == ttm_id).ttm_name; 128 // top5Studentid.Add(c.Split("_")[2]); 129 // if (!examName.Contains(_examName)) 130 // { 131 // examName.Add(_examName); 132 // } 133 // var lscore = new List<decimal>(); 134 // for (int i = 0; i < ttm_ids.Count; i++) 135 // { 136 // decimal totalscore = tempEveryStudentScoreInfoList.Where(c => c.ttm_id == ttm_ids[i] && c.per_id == per_id).Select(c => c.sttd_score).Sum(); 137 // lscore.Add(totalscore); 138 // } 139 // top5Score.Add(lscore.ToArray()); 140 //}); 141 #endregion 142 143 data.examNames = examName; 144 data.studentName = studentName; 145 data.studentEveryExamTotalScore = top5Score; 146 } 147 return data; 148 }
2：传说中的公式：
3：测试ok，数据格式正确返回，优化前5-6秒左右，优化之后1秒多一点就可以返回
4：需求二结果测试数据展示：
