sql统计－关于学生成绩

学生成绩表(stuscore)：

 

姓名：name

课程：subject

分数：score

学号：stuid

张三

数学

89

1

张三

语文

80

1

张三

英语

70

1

李四

数学

90

2

李四

语文

70

2

李四

英语

80

2

 

创建表 SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[stuscore](    [name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,    [subject] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,    [score] [int] NULL,    [stuid] [int] NULL) ON [PRIMARY] GOSET ANSI_PADDING OFF

问题：

1.    计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)

2.    计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)

3.    计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)

4.    计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）

5.    列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)

6.    列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)

7.    统计如下：

学号

姓名

语文

数学

英语

总分

平均分

 

 

 

 

 

 

 

 

8．列出各门课程的平均成绩（要求显示字段：课程，平均成绩）

9．列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）

10．列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）

11．求出李四的数学成绩的排名

12．统计如下：

课程

不及格（0-59）个

良（60-80）个

优（81-100）个

 

 

 

 

  

 

13．统计如下：数学:张三(50分),李四(90分),王五(90分),赵六(76分)

答案：

 

12. 课程    不及格（-59）   良（-80）   优（-100）

select  subject, ( select   count ( * )  from  stuscore  where  score < 60   and  subject = t1.subject)  as  不及格,( select   count ( * )  from  stuscore  where  score  between   60   and   80   and  subject = t1.subject)  as  良,( select   count ( * )  from  stuscore  where  score  > 80   and  subject = t1.subject)  as  优 from  stuscore t1  group   by  subject 13. 数学: 张三 (50 分 ), 李四 (90 分 ), 王五 (90 分 ), 赵六 (76 分 ) declare   @s   varchar ( 1000 ) set   @s = '' select   @s   = @s + ' , ' + name + ' ( ' + convert ( varchar ( 10 ),score) + ' 分) '   from  stuscore  where  subject = ' 数学 '   set   @s = stuff ( @s , 1 , 1 , '' ) print   ' 数学: ' + @s

 

1.       计算每个人的总成绩并排名

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

2.   计算每个人的总成绩并排名

select   distinct  t1.name,t1.stuid,t2.allscore  from   stuscore t1,(     select  stuid, sum (score)  as  allscore  from  stuscore  group   by  stuid)t2 where  t1.stuid = t2.stuid order   by  t2.allscore  desc

3. 计算每个人单科的最高成绩

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

4.计算每个人的平均成绩

select   distinct  t1.stuid,t1.name,t2.avgscore  from  stuscore t1,( select  stuid, avg (score)  as  avgscore  from  stuscore  group   by  stuid) t2 where  t1.stuid = t2.stuid

5.列出各门课程成绩最好的学生

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

6.列出各门课程成绩最好的两位学生

select   distinct  t1. *   from  stuscore t1  where  t1.stuid  in  ( select   top   2  stuscore.stuid  from  stuscore  where  subject  =  t1.subject  order   by  score  desc )  order   by  t1.subject

 

7.学号    姓名    语文     数学     英语     总分  平均分

 

select  stuid  as  学号,name  as  姓名, sum ( case   when  subject = ' 语文 '   then  score  else   0   end )  as  语文, sum ( case   when  subject = ' 数学 '   then  score  else   0   end )  as  数学, sum ( case   when  subject = ' 英语 '   then  score  else   0   end )  as  英语, sum (score)  as  总分,( sum (score) / count ( * ))  as  平均分 from  stuscore group   by  stuid,name  order   by  总分desc

8.列出各门课程的平均成绩

select  subject, avg (score)  as  avgscore  from  stuscore group   by  subject

9.列出数学成绩的排名

declare   @tmp   table (pm  int ,name  varchar ( 50 ),score  int ,stuid  int ) insert   into   @tmp   select   null ,name,score,stuid  from  stuscore  where  subject = ' 数学 '   order   by  score  desc declare   @id   int set   @id = 0 ; update   @tmp   set   @id = @id + 1 ,pm = @id select   *   from   @tmp  

 

select   DENSE_RANK ()  OVER ( order   by  score  desc )  as  row,name,subject,score,stuid  from  stuscore  where  subject = ' 数学 ' order   by  score  desc

 

declare   @tmp   table (pm  int   identity ( 1 , 1 ),name  varchar ( 50 ),score  int ,stuid  int ) insert   into   @tmp   select  name,score,stuid  from  stuscore  where  subject = ' 数学 '   order   by  score  desc select   *   from   @tmp

 

10. 列出数学成绩在2-3名的学生

select  t3. *    from ( select   top   2  t2. *    from  ( select   top   3  name,subject,score,stuid  from  stuscore  where  subject = ' 数学 ' order   by  score  desc ) t2  order   by  t2.score) t3  order   by  t3.score  desc

11. 求出李四的数学成绩的排名

declare   @tmp   table (pm  int ,name  varchar ( 50 ),score  int ,stuid  int ) insert   into   @tmp   select   null ,name,score,stuid  from  stuscore  where  subject = ' 数学 '   order   by  score  desc declare   @id   int set   @id = 0 ; update   @tmp   set   @id = @id + 1 ,pm = @id select   *   from   @tmp   where  name = ' 李四 '