数据统计与分析

首先你呢，先给一个方法，再把我所需要的AcademeID、GradeID、ClassID写进来。
public ActionResult SelectStatisticsData1(int AcademeID, int GradeID, int ClassID)
在获取一下搜索条件参数，用Linq查询数据引入session。把第一次查询到的数据放到session中，当第二次查询条件与第一次完全一致的时候可以直接从session中获取数据会加快速度。
List listResult = new List();
string strSearch = string.Format(“AcademeID={0}&GradeID={1}&ClassID={2}”, AcademeID, GradeID, ClassID);
来一个if、else。
if (Session[“strSearch”] != null && Session[“strSearch”].ToString() == strSearch && Session[“listAchievementHistory”] != null) {
listResult = Session[“listAchievementHistory”] as List;}
else{
数据筛选
var listAchievement = from tbAchievement in myModels.PW_Achievement
group tbAchievement by tbAchievement.UserID into tbStudent
select new{
UserID = tbStudent.Key,//key属性：返回进行分组的关键字段的值
AchievementID = tbStudent.OrderByDescending(m => m.Achievement).FirstOrDefault().AchievementID,//最优成绩的成绩ID
Achievement = tbStudent.OrderByDescending(m => m.Achievement).FirstOrDefault().Achievement,//最优成绩
ExamNumber = tbStudent.OrderByDescending(m => m.ExamNumber).FirstOrDefault().ExamNumber,//考试次数
EligibleTypeID = tbStudent.OrderByDescending(m => m.Achievement).FirstOrDefault().EligibleTypeID//合格类型ID };
再联表查询
listResult = (from tbAchievement in listAchievement
join tbStudent in myModels.PW_Student on tbAchievement.UserID equals tbStudent.UserID
join tbClass in myModels.SYS_Class on tbStudent.ClassID equals tbClass.ClassID
orderby tbAchievement.Achievement descending
join tbEligibleType in myModels.SYS_EligibleType on tbAchievement.EligibleTypeID equals tbEligibleType.EligibleTypeID
select new AchievementVo{
StudentNumber = tbStudent.StudentNumber,
StudentName = tbStudent.StudentName,
StudentSex = tbStudent.StudentSex,
Class = tbClass.ClassName,
ExamNumber = tbAchievement.ExamNumber,
Achievement = tbAchievement.Achievement,
EligibleType = tbEligibleType.EligibleTypeName,
AcademeID = tbStudent.AcademeID,
GradeID = tbStudent.GradeID,
ClassID = tbClass.ClassID}).ToList();
根据查询条件筛选if (AcademeID > 0) {
listResult = listResult.Where(m => m.AcademeID == AcademeID).ToList();}
if (GradeID > 0) {
listResult = listResult.Where(m => m.GradeID == GradeID).ToList();}
if (ClassID > 0) {
listResult = listResult.Where(m => m.ClassID == ClassID).ToList();}
Session[“strSearch”] = strSearch;
Session[“listAchievementHistory”] = listResult; }
获取总人数(三目运算：当前面的判断为true时，返回值为冒号前面的值，否则为冒号后面的值) int totleCount = listResult.Count() == 0 ? 1 : listResult.Count();
创建实体，存放统计的数据
AchievementStatisticsVo listStatistics = new AchievementStatisticsVo();
总人数listStatistics.TotalNumber = totleCount;
总成绩(使用linq中的Sum方法，用于计算平均成绩)
float sumAchievement = (float)listResult.Sum(m => m.Achievement);
平均成绩listStatistics.AverageScore = sumAchievement / totleCount;
计算成绩合格的人数&合格率
listStatistics.PassNumber = listResult.Count(m => m.Achievement >= 60);
listStatistics.PassNumberP = (float)listStatistics.PassNumber / totleCount * 100;
计算成绩不合格的人数&不合格率
listStatistics.PassFail = listResult.Count(m => m.Achievement < 60);
listStatistics.PassFailP = (float)listStatistics.PassFail / totleCount * 100;
分数段Score100分的人数&比例
listStatistics.Score100 = listResult.Count(m => m.Achievement == 100);
listStatistics.Score100P = (float)listStatistics.Score100 / totleCount * 100;
分数段90-99分的人数和比例
listStatistics.Score9099 = listResult.Count(m => m.Achievement >= 90 && m.Achievement < 100);
listStatistics.Score9099P = (float)listStatistics.Score9099 / totleCount * 100;
分数段80-89分的人数和比例
listStatistics.Score8089 = listResult.Count(m => m.Achievement >= 80 && m.Achievement < 90);
listStatistics.Score8089P = (float)listStatistics.Score8089 / totleCount * 100;
分数段70-79分的人数和比例
listStatistics.Score7079 = listResult.Count(m => m.Achievement >= 70 && m.Achievement <= 79);
listStatistics.Score7079P = (float)listStatistics.Score7079 / totleCount * 100;
分数段60-69分的人数和比例
listStatistics.Score6069 = listResult.Count(m => m.Achievement >= 60 && m.Achievement <= 69);
listStatistics.Score6069P = (float)listStatistics.Score6069 / totleCount * 100;
return Json(listStatistics, JsonRequestBehavior.AllowGet); }
效果图如下图所示：