SQL Statistics Daquan Collection, mainly some of the implementation of statistical functions commonly used code, I hope that the need for friends to help.
1. Calculate each person's total and rank
Select Name,sum (Score) as Allscore from Stuscore Group by name ORDER by Allscore
2. Calculate each person's total and rank
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
Select T1.stuid,t1.name,t1.subject,t1.score from Stuscore T1, (select Stuid,max (Score) as Maxscore from Stuscore group by S TUID) T2where T1.stuid=t2.stuid and T1.score=t2.maxscore
4. Calculate the average score for 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 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 of the two students who have the best results in each course
Select DISTINCT t1.* from Stuscore T1 where t1.id in (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 score average
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, (SUM (Score)/count (*)) as average score F Rom Stuscoregroup by stuid,name ORDER BY total desc
8. List the average results of each course
Select Subject,avg (Score) as Avgscore from Stuscoregroup by subject
9. List the ranking of math scores
Declare @tmp table (PM int,name varchar (), 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 @[email protected]+1,[email protected] * F Rom @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 of students with a math score of 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 John Doe's mathematical achievements
Declare @tmp table (PM int,name varchar (), 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 @[email protected]+1,[email protected] * F Rom @tmp where name= ' John Doe '
12. Failing course (-59) Good (-80) Excellent (-100)
Select Subject, (select COUNT (*) from Stuscore where score<60 and Subject=t1.subject) as fails, (SELECT COUNT (*) from Stus Core where score between and subject=t1.subject) as benign, (select COUNT (*) from Stuscore where score >80 and SUBJ Ect=t1.subject) as excellent from Stuscore T1 GROUP by subject
13. Mathematics: Zhang San (50 points), John Doe (90 points), Harry (90 points), Zhao Liu (76 points)
DECLARE @s varchar (+) Set @s= ' select @s [email protected]+ ', ' +name+ ' (' +convert (varchar), score) + ') ' from Stuscore where subject= ' math ' Set @s=stuff (@s,1,1, ') print ' math: ' [email protected]
SQL Server Statistical Statement collection
