Images, in turn, is a Student, StudentClass, Class three tables, as shown in figure, idea is to query to a Student number, and query the Student id number corresponding to different courses and grades, and query number corresponding to an average of each course, students will return to corresponding course number and average score comparison, judgment was born are output under the Student number that the door, but everyone to check a check scored an average of a certain course seem somewhat redundant,

CodePudding user response:

1. To find out first scored an average of each course

 
Select the convert (float, the sum (grade))/convert (float, count (grade)) as avgGrae, cno from studentclass group by cno 

2. To find a way to let the result to the select statement

 
The select s.s no, s.s name, Arthur c. name, sc. Grade the from student s 
Inner join 
Studentclass sc on s.s no=sc. Sno 
Inner join 
Class c on sc. Cno=Arthur c. no -- -- so far is to get all the data, and then connected to the detected average 
Inner join 
(select the convert (float, the sum (grade))/convert (float, count (grade)) as avgGrae, cno from # studentclass group by cno) gra 
On sc. Cno=gra. Cno 
Where the convert (float, sc. Cno) & gt; Gra. AvgGrae -- -- through the result column is greater than average respectively, and to filter the data 

CodePudding user response:

Finally the where to write wrong,
Where the convert (float, sc. Grade) & gt; Gra. AvgGrae