SQL Server statistics collection

1. Calculate the total score and rank each person

Select name, sum (score) as allscore from stuscore group by name order by allscore

2. Calculate the total score and rank each person

Select distinct t1.name, t1.stuid, t2.allscore from stuscore T1, (select stuid, sum (score) as allscore from stuscore group by stuid) t2where t1.stuid = t2.stuidorder by t2.allscore DESC

3. Calculate the highest score for each individual subject

Select t1.stuid, t1.name, t1.subject, t1.score from stuscore T1, (select stuid, max (score) as maxscore from stuscore group by stuid) t2where t1.stuid = t2.stuid and t1.score = t2.maxscore

4. Calculate the average score of each person

Select distinct t1.stuid, t1.name, t2.avgscore from stuscore T1, (select stuid, AVG (score) as avgscore from stuscore group by stuid) t2where t1.stuid = t2.stuid

5. List the students with the best scores for 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 the two students with the best scores in each course

Select distinct T1. * From stuscore T1 where t1.id in (select Top 2 stuscore. ID from stuscore where subject = t1.subject order by score DESC) order by t1.subject

7. Student ID name: average score of the total score of Chinese mathematics and English

Select stuid as student ID, name as name, sum (case when subject = 'China' then score else 0 end) as language, sum (case when subject = 'mate' then score else 0 end) as mathematics, sum (case when subject = 'en 'Then score else 0 end) as English, sum (score) as total score, (sum (score)/count (*) as average score from stuscoregroup by stuid, name order by total score DESC

8. List the average scores of each course

Select subject, AVG (score) as avgscore from stuscoregroup by subject

9. List the rankings of mathematical scores

Declare @ TMP table (PM int, name varchar (50), score int, stuid INT) insert into @ TMP select null, name, score, stuid from stuscore where subject = 'mate' 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 = 'mate' order by score DESC

10. List 2-3 students with scores in Mathematics

Select T3. * from (select Top 2 T2. * from (select top 3 name, subject, score, stuid from stuscore where subject = 'mate' order by score DESC) T2 order by t2.score) t3 order by t3.score DESC

11. Find the ranking of Li Si's mathematical score

Declare @ TMP table (PM int, name varchar (50), score int, stuid INT) insert into @ TMP select null, name, score, stuid from stuscore where subject = 'mate' order by score descdeclare @ ID intset @ ID = 0; update @ TMP set @ ID = @ ID + 1, PM = @ idselect * From @ TMP where name = 'Li si'

12. Failed course (-59) Good (-80) Excellent (-100)

Select subject, (select count (*) from stuscore where score <60 and subject = t1.subject) as fail, (select count (*) from stuscore where score between 60 and 80 and subject = t1.subject) as good, (select count (*) from stuscore where score> 80 and subject = t1.subject) as excellent from stuscore T1 group by subject

13. Mathematics: Zhang San (50 points), Li Si (90 points), Wang Wu (90 points), Zhao Liu (76 points)

Declare @ s varchar (1000) set @ s = ''select @ s = @ s + ',' + name + '(' + convert (varchar (10), score) + 'points) 'from stuscore where subject = 'mate' set @ s = stuff (@ s, '') print 'math:' + @ s