学生成绩表Score有三个字段
StudentID(varchar(8),学生编号)
CourseID(varchar(10),课程编号)
Score(int,成绩)问题:用sql语句筛选出每门课程的最高分、最低分,并且该课程最高分和最低分差距不能超过45分。
StudentID(varchar(8),学生编号)
CourseID(varchar(10),课程编号)
Score(int,成绩)问题:用sql语句筛选出每门课程的最高分、最低分,并且该课程最高分和最低分差距不能超过45分。
(
select CourseID , max(Score) max_score, min(Score) min_score from tb group by CourseID
) t
where max_score - min_score <= 45
(
select CourseID , max(Score) max_score, min(Score) min_score from score group by CourseID
) t
where max_score - min_score <= 45
select CourseID,Max(Score),Min(Score)
from score
group by CourseID
having max(score)-min(score)<=45
from score
group by CourseID
having max(score)-min(score)<=45
create table Score(StudentID varchar(8) ,CourseID varchar(10) , Score int)
insert into score values('1' , '1' , 1)
insert into score values('2' , '1' , 20)
insert into score values('3' , '1' , 11)
insert into score values('4' , '1' , 30)
insert into score values('5' , '1' , 2)
insert into score values('1' , '2' , 1)
insert into score values('2' , '2' , 20)
insert into score values('3' , '2' , 11)
insert into score values('4' , '2' , 80)
insert into score values('5' , '2' , 2)
goselect CourseID , max(Score) max_score, min(Score) min_score from score group by CourseID
having max(Score) - min(score) <= 45drop table score/*
CourseID max_score min_score
---------- ----------- -----------
1 30 1(所影响的行数为 1 行)
*/
select CourseID,min(Score),max(Score)
group by CourseID
having max(Score)-min(Score)<=45?