SQL Server Statistics The Encyclopedia collection

1. Calculate each person's total score and rank

Select Name,sum (Score) as Allscore from Stuscore Group by name ORDER by Allscore

2. Calculate each person's total score and rank

Select DISTINCT T1.name,t1.stuid,t2.allscore from Stuscore T1, (select Stuid,sum (Score) as Allscore to Stuscore Group by STUID) T2where T1.stuid=t2.stuidorder by T2.allscore Desc

3. Calculate the highest score for each individual section

Select T1.stuid,t1.name,t1.subject,t1.score from Stuscore T1 (select Stuid,max (Score) as Maxscore to Stuscore group by S TUID) T2where T1.stuid=t2.stuid and T1.score=t2.maxscore

4. Calculate the average score per person

Select DISTINCT T1.stuid,t1.name,t2.avgscore from Stuscore T1, (select Stuid,avg (Score) as Avgscore to Stuscore Group by STUID) T2where T1.stuid=t2.stuid

5. List the students with the best grades in each course

Select T1.stuid,t1.name,t1.subject,t2.maxscore from Stuscore T1 (select Subject,max (Score) as Maxscore from Stuscore Group by subject) T2where T1.subject=t2.subject and T1.score=t2.maxscore
6. List two students with the best grades in each course

Select DISTINCT t1.* from Stuscore T1 where t1.id into (select top 2 stuscore.id from stuscore where subject = T1.subject or Der by score desc) Order by T1.subject

7. School Number name Chinese mathematics English total score average score

Select Stuid as number, name as name, sum (case when subject= ' language ' then score else 0 end) as language, sum (case when subject= ' math ' then Scor E else 0 end) as math, sum (case when subject= ' English ' then score else 0 end) as English, sum (score) as Total score, (SUM (Score)/count (*)) as average cent f Rom Stuscoregroup by stuid,name ORDER BY total desc

8. List the average grades of each course

Select Subject,avg (Score) as Avgscore from Stuscoregroup by subject

9. List the rankings of math scores

Declare @tmp table (PM int,name varchar (m), score Int,stuid int) insert INTO @tmp select Null,name,score,stuid from Stuscore where subject= ' math ' ORDER by score Descdeclare @id intset @id =0;update @tmp Set @id = @id +1,pm= @idselect * from @tmp

Select Dense_rank () over (order BY score Desc) as Row,name,subject,score,stuid from Stuscore where subject= ' math ' ORDER by SC Ore desc

10. List students with math scores in 2-3

Select t3.* from (select top 2 t2.* from (select Top 3 name,subject,score,stuid from Stuscore where subject= ' math ' ORDER by SC Ore desc) t2 ORDER by T2.score) T3 ORDER BY T3.score Desc

11. Find out the ranking of Dick's mathematical achievements

Declare @tmp table (PM int,name varchar (m), score Int,stuid int) insert INTO @tmp select Null,name,score,stuid from Stuscore where subject= ' math ' ORDER by score Descdeclare @id intset @id =0;update @tmp Set @id = @id +1,pm= @idselect * from @tmp where n Ame= ' Dick '

12. The course failed (-59) Good (-80) Excellent (-100)

Select Subject, (select COUNT (*) from Stuscore where score<60 and Subject=t1.subject) as failed, (select COUNT (*) from Stus The core where score between and subject=t1.subject) as good, (select COUNT (*) from Stuscore where score >80 and SUBJ Ect=t1.subject) as excellent from Stuscore T1 GROUP by subject

13. Mathematics: John (50 points), Dick (90 points), Harry (90 points), Zhao Liu (76 points)

DECLARE @s varchar (1000) Set @s= ' Select @s =@s+ ', ' +name+ ' (' +convert ', varchar) + ' cent ' from score where stuscore = ' Math ' Set @s=stuff (@s,1,1, ') print ' math: ' +@s