查询低于平均分的学员信息?

Question

1.我建了一个成绩表:score  StudentID nchar(10)    --学号
  CourseID nchar(20)　　--课程ID,就是课程名称
  Score         float   --分数
  ScoreID nchar(10) --按照分数高低分为４类2.表中数据如下:
  StudentID     CourseID              Score  ScoreID
  1          sql                  82 B         
  10         sql                  69 D         
  2          HTML                 65 D         
  3          stb_1                75 C         
  4          SQL                  45 C         
  5          C#                   65 D         
  6          stb_1                75 C         
  7          HTML                 65 D         
  8          stb_2                71 D         
  9          HTML                 90 B  3.我想用group by,having子句和聚合函数,查询低于平均分的学员信息(StudentID,CourseID ,Score, ScoreID):
  我试了很多次都不成！！有没有老师，指点一下!!万分感谢!!
       

Answer 1

select a.* from score a left join (select CourseID,avg(Score) Score from score group by CourseID) b
on a.CourseID=b.CourseID and a.Score<b.score

Answer 2

--try
select s.* from score s,(
select avg(Score)Score ,CourseID from score group by CourseID)t where s.CourseID=t.CourseID
and s.Score<t.Score

Answer 3

低于单科平均分：
declare @score table(StudentID nchar(10),CourseID nchar(20),Score float,ScoreID nchar(10))insert into @score select  '1','sql  ',82,'B'        
insert into @score select '10','sql  ',69,'D'        
insert into @score select  '2','HTML ',65,'D'        
insert into @score select  '3','stb_1',75,'C'        
insert into @score select  '4','SQL  ',45,'C'        
insert into @score select  '5','C#   ',65,'D'        
insert into @score select  '6','stb_1',75,'C'        
insert into @score select  '7','HTML ',65,'D'        
insert into @score select  '8','stb_2',71,'D'        
insert into @score select  '9','HTML ',90,'B'  select
    a.*
from
    @score a,(select CourseID,AVG(Score) as Score from @score group by CourseID) b
where
    a.CourseID=b.CourseID and a.Score<b.Score/*
StudentID  CourseID             Score                                                 ScoreID    
---------- -------------------- ----------------------------------------------------- ---------- 
2          HTML                 65.0                                                  D         
7          HTML                 65.0                                                  D         
4          SQL                  45.0                                                  C 
*/

Answer 4

if object_id('tb') is not null drop table tb 
 go 
create table tb([StudentID] int,[CourseID] varchar(10),[Score] int,[ScoreID] varchar(10))
insert tb select 1,'sql',82,'B'
union all select 10,'sql',69,'D'
union all select 2,'HTML',65,'D'
union all select 3,'stb_1',75,'C'
union all select 4,'SQL',45,'C'
union all select 5,'C#',65,'D'
union all select 6,'stb_1',75,'C'
union all select 7,'HTML',65,'D'
union all select 8,'stb_2',71,'D'
union all select 9,'HTML',90,'B'
go
select a.* 
from tb a 
    left join (select CourseID,avg(Score) Score from tb group by CourseID) b
      on a.CourseID=b.CourseID 
where a.Score<isnull(b.score,0)
/*
StudentID   CourseID   Score       ScoreID
----------- ---------- ----------- ----------
2           HTML       65          D
4           SQL        45          C
7           HTML       65          D(3 行受影响)
*/

Answer 5

用嵌套
select * from score
where score<(select avg(score) from score)

Answer 6

select a.* from score a left join (select CourseID,avg(Score) Score from score group by CourseID) b
on a.CourseID=b.CourseID and a.Score<b.score

Answer 7

create table score(StudentID nvarchar(10),CourseID nvarchar(20),Score float,ScoreID nvarchar(10))
go
insert score select 1 ,       'sql'                ,82, 'B'        
insert score select 10 ,       'sql'                ,69, 'D'        
insert score select 2  ,      'HTML'                ,65, 'D'        
insert score select 3  ,      'stb_1'              ,75, 'C'        
insert score select 4  ,      'SQL'                ,45 ,'C'        
insert score select 5  ,      'C#'                  ,65, 'D'        
insert score select 6  ,      'stb_1'              ,75, 'C'        
insert score select 7  ,      'HTML'                ,65, 'D'        
insert score select 8  ,      'stb_2'              ,71 ,'D'        
insert score select 9  ,      'HTML'                ,90, 'B'  go
      
select a.* from score a inner join  (select CourseID,avg(Score) Score from score group by CourseID) b
on a.CourseID=b.CourseID and a.Score<b.score
go
drop table score
go
/*
StudentID  CourseID             Score                                                 ScoreID    
---------- -------------------- ----------------------------------------------------- ---------- 
2          HTML                 65.0                                                  D
7          HTML                 65.0                                                  D
4          SQL                  45.0                                                  C（所影响的行数为 3 行）
*/

Answer 8

这个手误，应该是inner join.

Answer 9

select studentid
from score t
where score<(select avg(score) from score  where CourseID =t.CourseID )

Answer 10

create table score
( 
StudentID nchar(10),--学号 
CourseID nchar(20),--课程ID,就是课程名称 
Score float,--分数 
ScoreID nchar(10)--按照分数高低分为４类 
)insert score
select '1','sql',82,'B' union all  
select '10','sql',69,'D' union all  
select '2','HTML',65,'D' union all  
select '3','stb_1',75,'C' union all  
select '4','SQL',45,'C' union all  
select '5','C#',65,'D' union all  
select '6','stb_1',75,'C' union all  
select '7','HTML',65,'D' union all  
select '8','stb_2',71,'D' union all 
select '9','HTML',90,'B'  select B.*
from (SELECT CourseID,AVG(Score) Score FROM score GROUP BY CourseID) A,score B
where A.CourseID=B.CourseID AND B.Score<A.Score/*
StudentID  CourseID             Score                  ScoreID
---------- -------------------- ---------------------- ----------
7          HTML                 65                     D         
2          HTML                 65                     D         
4          SQL                  45                     C         (3 行受影响)
*/

Answer 11

create table #score(StudentID nchar(10),CourseID nchar(20),Score float,ScoreID nchar(10))
insert into #score select  '1','sql  ',82,'B'        
insert into #score select '10','sql  ',69,'D'        
insert into #score select  '2','HTML ',65,'D'        
insert into #score select  '3','stb_1',75,'C'        
insert into #score select  '4','SQL  ',45,'C'        
insert into #score select  '5','C#   ',65,'D'        
insert into #score select  '6','stb_1',75,'C'        
insert into #score select  '7','HTML ',65,'D'        
insert into #score select  '8','stb_2',71,'D'        
insert into #score select  '8','stb_2',71,'D'        
insert into #score select  '9','HTML ',90,'B'select * from #score where Score<(select avg(score) score from #score)StudentID  CourseID             Score                  ScoreID
---------- -------------------- ---------------------- ----------
10         sql                  69                     D         
2          HTML                 65                     D         
4          SQL                  45                     C         
5          C#                   65                     D         
7          HTML                 65                     D         (5 行受影响)

Answer 12

create table #score(StudentID nchar(10),CourseID nchar(20),Score float,ScoreID nchar(10))
insert into #score select  '1','sql  ',82,'B'        
insert into #score select '10','sql  ',69,'D'        
insert into #score select  '2','HTML ',65,'D'        
insert into #score select  '3','stb_1',75,'C'        
insert into #score select  '4','SQL  ',45,'C'        
insert into #score select  '5','C#   ',65,'D'        
insert into #score select  '6','stb_1',75,'C'        
insert into #score select  '7','HTML ',65,'D'        
insert into #score select  '8','stb_2',71,'D'        
insert into #score select  '8','stb_2',71,'D'        
insert into #score select  '9','HTML ',90,'B'
--(1)小于平均分的学生
select * from #score where Score<(select avg(score) score from #score)
--结果
StudentID  CourseID             Score                  ScoreID
---------- -------------------- ---------------------- ----------
10         sql                  69                     D         
2          HTML                 65                     D         
4          SQL                  45                     C         
5          C#                   65                     D         
7          HTML                 65                     D         (5 行受影响)
--(2)先求出各科成绩的平均分,然后刷选出比平均分小的
select s.StudentID,s.Score,c.CourseID from #score s
join
(
 select avg(Score) savg,CourseID from #score group by CourseID
) c  on s.CourseID=c.CourseID where s.Score<c.savg
--结果
StudentID  Score                  CourseID
---------- ---------------------- --------------------
7          65                     HTML                
2          65                     HTML                
4          45                     SQL                 (3 行受影响)

Answer 13

多谢,其实我本意是想用having来求，看来也没更好的办法。
不过，受益了，对于这种嵌套查询:把'(.....)'当作对象来用，我也会点，不过在内连接时的这种还没用过.多谢多谢!