SQL finds records with the highest distribution of scores from the Student scores table

Title: There is a data sheet student, containing name (name), dept (subject), score (score) Three fields, now requires to find out the most distribution of scores in each section, such as the language score of 80 points, the number of the output language 80 points, math 90 points the most people, The number of people who output mathematics 90 points;

Test data:

INSERT into student values ("Deng", "80", "language"), insert into student values ("Zhang", "80", "language"), insert into student values (" Li "," 70 "," language "), insert into student values (" Xiong "," 85 "," language "), insert into student values (" Bao "," 60 "," language "); INSERT into Student VALUES ("King", "80", "language"), insert into student values ("Ying", "90", "language"), insert into student values ("Hong", "60", "Language");  insert into student values ("Deng", "90", "mathematics"), insert into student values ("Zhang", "80", "math"); INSERT into Student values ("Li", "80", "mathematics"), insert into student values ("Xiong", "65", "math"), insert into student values ("Bao", "60", " Math ") insert into student values (" King "," 80 "," math "), insert into student values (" Ying "," 90 "," math "), insert into student Valu ES ("Hong", "60", "mathematics");  insert into student values ("Deng", "70", "English"), insert into student values ("Zhang", "85", "English" INSERT into student values ("Li", "85", "English"), insert into student values ("Xiong", "65", "English"), insert into student values (" Bao "," 60 "," English "); INSERT into student valueS ("King", "85", "English"); INSERT into student values ("Ying", "70", "English"), insert into student values ("Hong", "65", "English");  First step: Create a View view2: Get the number of records of the scores of each section; and according to the account, the number of records is the size of the flashback sequence; create View view2 as select Dept,score,count (score) as Countnum from Student GROUP BY Dept,score ORDER by dept,countnum Desc; Step Two: Create a view view3: Get a list of all the accounts; CREATE View view3 as select Dept Fro M student GROUP by dept;  step three: Left join answer:   Get the first record of each section; Select View3.dept,view2.score,view2.countnum from VIEW3 Left join View2 on view3.dept=view2.dept GROUP by view3.dept;

The second and third steps can be combined to optimize SELECT * from VIEW2 Group BY dept; The final result can be obtained directly, or: Select Dept,score,max (countnum) from View2 Group by dept; One problem is that there are two records of the distribution of scores in a particular section; select * from View2 where Countnum in (select Max (countnum) from View2 Group by dept);

SQL finds records with the highest distribution of scores from the Student scores table