sql--Statistical Query

The data table is as follows:
I'm using a MySQL database, so the relevant SQL is

2. CREATE TABLE ' Stuscore ' (
4. ' ID ' int (one) not NULL auto_increment,
6. 'name ' varchar ( default NULL),
8. ' Subject ' varchar default NULL,
10. ' Score ' varchar default NULL,
12. ' Stuid ' varchar default NULL,
14. PRIMARY KEY (' id ')
16. ) Engine=innodb DEFAULT Charset=utf8;
20. /*data for the table ' stuscore ' * /
24. Insert INTO ' Stuscore ' (' id ', 'name ', ' Subject ', ' score ', ' stuid ') values
26. (1,' Zhang San ',' math ',' the '1 '),
28. (2,' Zhang San ',' language ', ' a ', '1 '),
30. (3,' Zhang San ',' English ',' all ',' 1 '),
32. (4,' John Doe ',' math ',' all ',' 2 '),
34. (5,' John Doe ',' language ','2 ') ,
36. (6,' John Doe ',' English ',' n ',' 2 ');

The following are the related questions:

1. Calculate the total number of each person and rank (requires display field: Name, overall scores)
2. Calculate each person's total and rank (required field: number, name, overall)
3. Calculate the highest score for each individual (required field: School number, name, course, highest score)
4. Calculate each person's average score (requires display field: study number, name, average score)
5. List the students who have the best grades in each course (Required fields: Student number, name, subject, score)
6. List of the two students who have the best results in each course (Required fields: Student number, name, subject, score)

7. The statistics are as follows:

5. List the students who have the best grades in each course (Required fields: Student number, name, subject, score)
6. List of the two students who have the best results in each course (Required fields: Student number, name, subject, score)
8. List the average scores for each course (Required fields: course, average)
9. List the ranking of math scores (required fields: School number, name, score, rank)
10. List of students with a mathematical score of 2-3 (Requirements display field: Student number, name, subject, score)

11. Find out the ranking of John Doe's mathematical achievements

13. The statistics are as follows: Mathematics: Zhang San (50 points), John Doe (90 points), Harry (90 points), Zhao Liu (76 points)

The answers are as follows:

1. Calculate the total number of each person and rank (requires display field: Name, overall scores)

1. Select name,sum(score) as Allscore from stuscore group by name order by al Lscore desc

2. Calculate each person's total and rank (required field: number, name, overall)

1. Select Stuid,name,sum(score) as Allscore from stuscore group by name order C11>by allscore desc

3. Calculate the highest score for each individual (required field: School number, name, course, highest score)

1. SELECT t1.stuid,t1. Name,t1.subject,t1.score from stuscore T1,
2. (SELECT Stuid,Max (score) as Maxscore from stuscore Group by stuid) T2
3. where T1.stuid=t2.stuid and T1.score=t2.maxscore

4. Calculate each person's average score (requires display field: study number, name, average score)

1. Select distinct t1.stuid,t1. Name,t2.avgscore from stuscore T1,
2. (Select Stuid,avg(Score) as Avgscore from stuscore Group by stuid) T2
3. where T1.stuid=t2.stuid

5. List the students who have the best grades in each course (Required fields: Student number, name, subject, score)

1. Select T1.stuid,t1. Name,t1.subject,t2.maxscore from stuscore T1,
2. (Select Subject,max (score) as Maxscore from stuscore Group by subject) T2 /c5>
3. where T1.subject=t2.subject and T1.score=t2.maxscore

6. List of the two students who have the best results in each course (Required fields: Student number, name, subject, score)

1. Select distinct t1.* from stuscore T1 where T1.stuid in
2. (Select Top 2 stuscore.stuid from stuscore where subject = T1.subject ORDER by score desc)
3. ORDER BY T1.subject

The statement is not tested and is now known to be unable to run under MySQL. MySQL does not support select top.

7.

2. Select Stuid as school number,name as name,
4. sum (Case when subject=' language ' then score else 0 end) as language, /c9>
6. sum (Case when subject=' math ' then score else 0 end) as math,
8. sum (Case when subject=' English ' then score else 0 end) as English,
10. sum (score) as Score, (sum(score)/Count(*)) as average
12. From Stuscore
14. Group by Stuid,name
16. ORDER by Total desc

8. List the average results of each course (required field: course, average)

1. Select Subject,avg(Score) as Avgscore from stuscore GROUP by subject

9. List the ranking of math scores (Required fields: study number, name, score, rank)

1. SELECT * from stuscore where subject =' math ' order by score desc

I don't know what I think, right?

10. List of students with a math score of 2-3 (Requirements display field: Student number, name, subject, score)

2. Select t3.* from (
4. Select Top 2 t2.* from (
6. Select Top 3 name,subject,score,stuid from stuscore where subject=' math '
8. ORDER by score desc
10. ) T2 ORDER by T2.score
12. ) T3 ORDER by T3.score desc

11. Find out the ranking of John Doe's mathematical achievements

2. DECLARE @tmp table (PM int,name varchar (score), int,stuid int)
4. Insert INTO @tmp select null,name,score,stuid from stuscore where subject=' math ' ORDER by score desc
6. DECLARE @id int
8. Set @id = 0;
10. Update @tmp set @[email protected]+1,[email protected]
12. SELECT * from @tmp where name=' John Doe '

12.

2. Select Subject,
4. (Select Count(*) from Stuscore where score<60 and subject=t1.subject) as failed,
6. (Select Count(*) from Stuscore where score between and Subject=t1.subject) as good,
8. (Select Count(*) from Stuscore where score >80 and subject= T1.subject) as excellent
10. From Stuscore T1 GROUP by subject

13. The statistics are as follows: Mathematics: Zhang San (50 points), John Doe (90 points), Harry (90 points), Zhao Liu (76 points)

1. DECLARE @s varchar (+)
2. Set @s="
3. Select @s [Email protected]+', ' +name+' (' +convert(varchar), score) +') ' From Stuscore where subject=' mathematics '
4. Set @s=stuff (@s,1,1,")
5. Print ' math: ' [email protected]

Note: section 10,11,13 is not verified.

Original from: http://www.cnblogs.com/tenghoo/archive/2007/06/11/779240.htm

Good text to the top of my collection of the article Pato '
Follow-6
Fans-31 + plus attention

sql--Statistical Query