Query and statistical operation of tables

I. data query

1. Requirements: find out the highest score information

Statement Description: Linq uses group
By group query the user's highest score information by user ID, group syntax: group tbAchievement
by tbAchievement.UserID into tbStudent indicates that tbAchievement is grouped by UserID field, and the result is named tbStudent.

Get the first value in descending order, which is the maximum value we need.

var listAchievement = from tbAchievement in
myModels.PW_Achievement

                    
group tbAchievement by tbAchievement.UserID into tbStudent

select new {

UserID = tbStudent.Key,//Key attribute: returns the value of the key field for grouping

AchievementID = tbStudent.OrderByDescending(m =>
m.Achievement).FirstOrDefault().AchievementID,

                      Achievement =
tbStudent.OrderByDescending(m =>
m.Achievement).FirstOrDefault().Achievement,

                      ExamNumber =
tbStudent.OrderByDescending(m => m.ExamNumber).FirstOrDefault().ExamNumber,

                      EligibleTypeID =
tbStudent.OrderByDescending(m =>
m.Achievement).FirstOrDefault().EligibleTypeID

                                  };

2. Use multi table associated query method to find the best score, the most examination times, student number and other information into the new entity class achivementvo

II. Data filtering

1. First, instantiate the fields such as total number of people, average value, proportion of each score segment & number of people in achievement statistics vo.

AchievementStatisticsVo
listStatistics = new AchievementStatisticsVo();

2. Calculate the total number of people by using the three item operation

int totalRow = listResult.Count()==0?1:listResult.Count();

listStatistics.TotalNumber= totalRow;

3. Calculate the total score of all students

float sumAchievement =(float)listResult.Sum(m=> m.Achievement);

3. Calculate the average score (total score / total number of people)

listStatistics.AverageScore= sumAchievement / totalRow;

4. Calculate qualified number and qualified proportion (qualified number / total number)

listStatistics.PassNumber= listResult.Count(m => m.Achievement >= 60);

listStatistics.PassNumberP= (float)listStatistics.PassNumber / totalRow * 100;

5. Calculate the number of unqualified people and unqualified proportion

listStatistics.PassFail= listResult.Count(m => m.Achievement < 60);

listStatistics.PassFailP= (float)listStatistics.PassFail / totalRow * 100;

6. Calculate 100 people and proportion

listStatistics.Score100= listResult.Count(m => m.Achievement == 100);

listStatistics.Score100P= (float)listStatistics.Score100 / totalRow * 100;

7. Calculate 90 ` 99 people and proportion

listStatistics.Score9099= listResult.Count(m => m.Achievement >= 90 && m.Achievement <100);

listStatistics.Score9099P= (float)listStatistics.Score9099 / totalRow * 100;

1. Calculate 80 ` 89 people and proportion

listStatistics.Score8089=

listResult.Count(m => m.Achievement >= 80 && m.Achievement <90);

listStatistics.Score8089P= (float)listStatistics.Score8089 / totalRow * 100;

Push down next time, and finally return to the listStatistics data page