.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 mytempdatatable01
                            SELECT tu.unit_session_uid,us.unit_session_sdate,us.unit_session_edate FROM unit tu 
                            INNER 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 distinct
                               U.ttm_id,U.ttm_name,U.uni_id  ,U.stt_avgscore ,U.stt_id,U.class_uid
                             FROM(
                                   SELECT distinct
                                   M.ttm_id,M.ttm_name,D.class_uid,M.uni_id,SM.cou_id ,SM.stt_avgscore,TD.stt_id,C.class_level
                                    FROM test_main M
                                   LEFT JOIN test_detail D ON M.ttm_id=D.ttm_id 
                                 LEFT JOIN school_test_transcripts SM ON M.ttm_id=SM.ttm_id
                                 LEFT JOIN school_test_transcripts_detail TD ON SM.stt_id=TD.stt_id 
                                 INNER JOIN class C ON D.class_uid=C.class_uid
                                   WHERE 1=1                              
                                       AND M.uni_id=@uni_id
                                         AND SM.uni_id=@uni_id
                                       AND 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<=@ed  
                                   GROUP BY  M.uni_id,M.ttm_id,M.ttm_name,TD.stt_id
                               )U
                                   INNER JOIN unit_session S ON U.uni_id=S.uni_id AND S.unit_session_type=1
                                   AND U.stt_avgscore IS NOT null 
                                    AND U.uni_id=@uni_id
                                   WHERE U.class_level=@class_level
                                   GROUP 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>(@"
                                                       SELECT 
                                                            M.ttm_id,D.stt_id,D.per_id,D.sttd_score,P.per_name
                                                             FROM
                                                        school_test_transcripts M 
                                                         LEFT join school_test_transcripts_detail D on M.stt_id=D.stt_id
                                                        INNER JOIN personnel P ON D.per_id=P.per_id AND P.isdel=0
                                                        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();
                    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());
                //}); 
                #endregion

                data.examNames = examName;
                data.studentName = studentName;
                data.studentEveryExamTotalScore = top5Score;
            }
            return data;
        }

2：传说中的公式：

 

 3：测试ok，数据格式正确返回，优化前5-6秒左右，优化之后1秒多一点就可以返回

 4：需求二结果测试数据展示：