SQL Server queries record averages and sorted statements
Query the student's average score and rank, SQL 2000 is completed with a subquery, when the average score is repeated, the position is left vacant and two kinds of positions are not retained.
Select t1.*, px = (select count (1) from
(
Select m.s# [student number],
     &N bsp; M.sname [student name],
isnull (avg (score) as Decimal (18,2)), 0 [average score]
from Student m left join SC n on m.s# = n.s#
GROUP by m.s#, M.sname
) T2 where average score > T1. Average score + 1 from
(
Select m.s# [student number],
&nbs p; M.sname [student name],
isnull (CAST (avg. score) as decimal (18,2 ), 0) [average score]
from Student m left join SC n on m.s# = n.s#
GROUP by m.s#, M.sname
) T1
Orde R by PX
Select t1.*, px = (select COUNT (distinct average score) from
(
Select m.s# [student number],
M.sname [student name],
IsNull (CAST (AVG (score) as decimal (18,2)), 0 [average grade]
From Student M-left JOIN SC n on m.s# = n.s#
Group BY m.s#, M.sname
T2 where average score >= t1. Average score) from
(
Select m.s# [student number],
M.sname [student name],
IsNull (CAST (AVG (score) as decimal (18,2)), 0 [average grade]
From Student M-left JOIN SC n on m.s# = n.s#
Group BY m.s#, M.sname
) T1
ORDER by px
/*
Data table structure
--1. Student table
Student (s#,sname,sage,ssex)--s# student number, sname student name, Sage date of birth, ssex student sex
--2. Schedule
Course (c#,cname,t# --c#--Course number, Cname course name, t# teacher number
--3. Teacher's Table
Teacher (t#,tname)--t# teacher number, tname teacher's name
--4. Score Sheet
SC (s#,c#,score) --s# student number, C # course number, score score