.NetCore 3.1 WebApi 使用Mysql临时表Dapper与Linq处理报表

1：测试代码：Dapper集合Linq处理报表
传参数这块直接使用单个的参数了，嘻嘻
public async Task<TheMostUnitAndClassStudentDataShowokFormart> TheMostVolatileClassGetStudentExamInfo(string uni_id, int level, int gp_id, int gc_id, int ga_id){// --AND M.ttm_date >= @sd AND M.ttm_date <= @ed 暂时注释，方便测试var data = new TheMostUnitAndClassStudentDataShowokFormart{examNames = new List<string>(),studentEveryExamTotalScore = new List<decimal[]>(),studentName = new List<string>()};var listdata = new List<TheMostUnitAndClassGroupStudentDataShowList>();if (!uni_id.IsNullOrEmptyStr())//校级调用的时候,求波动最大的班级{string sql = @\"DROP temporary TABLE  if EXISTS mytempdatatable01;CREATE TEMPORARY table mytempdatatable01SELECT tu.unit_session_uid,us.unit_session_sdate,us.unit_session_edate FROM unit tuINNER JOIN unit_session us ON tu.uni_id=us.uni_id AND us.unit_session_type=\'1\'WHERE tu.uni_id=@uni_id AND tu.isdel=0  and us.isdel=0 LIMIT 1;SET @sd=\'1990-01-01\',@ed=\'1990-01-01\';SET @sd=(SELECT unit_session_sdate FROM mytempdatatable01);SET @ed=(SELECT unit_session_edate FROM mytempdatatable01);SELECT distinctU.ttm_id,U.ttm_name,U.uni_id  ,U.stt_avgscore ,U.stt_id,U.class_uidFROM(SELECT distinctM.ttm_id,M.ttm_name,D.class_uid,M.uni_id,SM.cou_id ,SM.stt_avgscore,TD.stt_id,C.class_levelFROM test_main MLEFT JOIN test_detail D ON M.ttm_id=D.ttm_idLEFT JOIN school_test_transcripts SM ON M.ttm_id=SM.ttm_idLEFT JOIN school_test_transcripts_detail TD ON SM.stt_id=TD.stt_idINNER JOIN class C ON D.class_uid=C.class_uidWHERE 1=1AND M.uni_id=@uni_idAND SM.uni_id=@uni_idAND M.isdel=0 AND D.isdel=0 AND TD.isdel=0 AND TD.score_type=0-- AND M.ttm_date>=@sd AND M.ttm_date<=@edGROUP BY  M.uni_id,M.ttm_id,M.ttm_name,TD.stt_id)UINNER JOIN unit_session S ON U.uni_id=S.uni_id AND S.unit_session_type=1AND U.stt_avgscore IS NOT nullAND U.uni_id=@uni_idWHERE U.class_level=@class_levelGROUP BY U.uni_id, U.ttm_id,U.ttm_name,U.stt_id; \";var modelFirst = await ZRF_DapperHelper.QueryAsync<TheMostUnitAndClassGroupStudentTempInfo>(sql, new { uni_id = uni_id, class_level = level });if (modelFirst == null || !modelFirst.Any())return data;var ttm_ids = modelFirst.Select(c => c.ttm_id).Distinct().ToList();//有多少次 ttm_id考试var tempDic = new Dictionary<string, decimal>();var tempEveryStudentScoreInfoList = new List<GetStudentThisExamPerIDAndScore>();for (int m = 0; m < ttm_ids.Count; m++){//每次考试有多少班级考试的次数，可能是同一个班级考试var everyExamStudentCoreInfo = (await ZRF_DapperHelper.QueryAsync<GetStudentThisExamPerIDAndScore>(@\"SELECTM.ttm_id,D.stt_id,D.per_id,D.sttd_score,P.per_nameFROMschool_test_transcripts MLEFT join school_test_transcripts_detail D on M.stt_id=D.stt_idINNER JOIN personnel P ON D.per_id=P.per_id AND P.isdel=0WHERE 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();if (everyExamStudentCoreInfo != null && everyExamStudentCoreInfo.Any()){tempEveryStudentScoreInfoList.AddRange(everyExamStudentCoreInfo);var examCout = everyExamStudentCoreInfo.Select(c => c.stt_id).Distinct().ToList();//其中考试 考了几次var disticntStudentPer_Id = everyExamStudentCoreInfo.Select(c => c.per_id).Distinct().ToList();//这一批考试的所有学生去重decimal getThisExamAvgScore = everyExamStudentCoreInfo.Select(c => c.sttd_score).Average();//这次考试的平均分for (int s = 0; s < disticntStudentPer_Id.Count; s++)//求每一个学生的标准差{string thisStudent = disticntStudentPer_Id[s];//这个学生的per_id//string studentName = everyExamStudentCoreInfo.FirstOrDefault(c => c.per_id == thisStudent).per_name;//decimal totalScore = everyExamStudentCoreInfo.Where(c => c.per_id == thisStudent).Select(c => c.sttd_score).Sum();//这个学生的总分decimal bzc = 0;List<decimal> thisStudentScore = everyExamStudentCoreInfo.Where(c => c.per_id == thisStudent).Select(c => c.sttd_score).ToList();for (int studentScorecount = 0; studentScorecount < thisStudentScore.Count; studentScorecount++)//该考了几次考试{bzc += (thisStudentScore[studentScorecount] - getThisExamAvgScore) * (thisStudentScore[studentScorecount] - getThisExamAvgScore);}double tempbzf = double.Parse(bzc.ToString()) * 1.0 / examCout.Count;bzc = decimal.Parse(Math.Sqrt(tempbzf).ToString(\"f2\"));//  listper_id.Add($\"{thisStudent}_{ttm_ids[m]}_{studentName}\", bzc);if (!tempDic.ContainsKey(thisStudent)){tempDic.Add(thisStudent, bzc);}}}}var dictinctDicStudent = tempDic.OrderByDescending(c => c.Value).Take(5).ToList();//不重复的5个学生List<string> examName = new List<string>();//考试的名称List<string> studentName = new List<string>();//学生名称List<decimal[]> top5Score = new List<decimal[]>();//学生分数foreach (var item in dictinctDicStudent){string per_id = item.Key;string per_name = tempEveryStudentScoreInfoList.FirstOrDefault(c => c.per_id == per_id).per_name;studentName.Add(per_name);List<decimal> everyExamScoreForStudent = new List<decimal>();for (int i = 0; i < ttm_ids.Count; i++){string exam_Name = modelFirst.FirstOrDefault(c => c.ttm_id == ttm_ids[i]).ttm_name;if (!examName.Contains(exam_Name)){examName.Add(exam_Name);}everyExamScoreForStudent.Add(tempEveryStudentScoreInfoList.Where(c => c.per_id == per_id && c.ttm_id == ttm_ids[i]).Select(c => c.sttd_score).Sum());}top5Score.Add(everyExamScoreForStudent.ToArray());}#region MyRegion  存在重复的逻辑//var diclist = listper_id.OrderByDescending(c => c.Value).Take(5).ToList();//List<string> top5Studentid = new List<string>();//学生id//List<decimal[]> top5Score = new List<decimal[]>();//学生分数//List<string> examName = new List<string>();////5个学生//diclist.Select(c => c.Key).ToList().ForEach(c =>//{//    string ttm_id = c.Split(\"_\")[1];//    string per_id = c.Split(\"_\")[0];//    string _examName = modelFirst.FirstOrDefault(c => c.ttm_id == ttm_id).ttm_name;//    top5Studentid.Add(c.Split(\"_\")[2]);//    if (!examName.Contains(_examName))//    {//        examName.Add(_examName);//    }//    var lscore = new List<decimal>();//    for (int i = 0; i < ttm_ids.Count; i++)//    {//        decimal totalscore = tempEveryStudentScoreInfoList.Where(c => c.ttm_id == ttm_ids[i] && c.per_id == per_id).Select(c => c.sttd_score).Sum();//        lscore.Add(totalscore);//    }//    top5Score.Add(lscore.ToArray());//});#endregiondata.examNames = examName;data.studentName = studentName;data.studentEveryExamTotalScore = top5Score;}return data;}
View Code
2：传说中的公式：