哪位前辈能帮我解下这两道题啊?1.有一STUDENT表如下:
StudentID(学生号),StudentName(学生名),course(课程名),CourseID(课程id),Score
面试题如下:
(1)用sql语句求出各科课程的最高分,显示格式如下:
StudentID,tudentName,Course,courseID,MaxScore +:
(2)求出学生的总分和平均分和四门课的成绩,并按照总分倒序排列,格式如下:
StudentID,StudentName,数学,语文,英语,物理,AvgScore,total
StudentID(学生号),StudentName(学生名),course(课程名),CourseID(课程id),Score
面试题如下:
(1)用sql语句求出各科课程的最高分,显示格式如下:
StudentID,tudentName,Course,courseID,MaxScore +:
(2)求出学生的总分和平均分和四门课的成绩,并按照总分倒序排列,格式如下:
StudentID,StudentName,数学,语文,英语,物理,AvgScore,total
select
StudentID,tudentName,Course,courseID,Score
from STUDENT s
where not exists(select * from STUDENT where courseID=s.courseID and Score>s.Score)
,case when course='语文' then score end as score2
,case when course='英语' then score end as score3
,case when course='物理' then score end as score4
,score1+score2+score3+score4 as all,(score1+score2+score3+score4)/4 as average
from tb
order by
group by CourseID
from(
select studentId,StudentName
max(case when course='数学' then score else null end) as score1
,max(case when course='语文' then score else null end) as score2
,max(case when course='英语' then score else null end) as score3
,max(case when course='物理' then score else null end) as score4
from tb
group by studentID,StudentName
)
前辈,我所见见过的sql中from都是选自于表,是不是该这样写啊select studentId,studentName,score1,score2,score3,score4,(score1+score2+score3+score4) as all,(score1+score2+score3+score4) /4 as avgScore
from from
where(
select studentId,StudentName
max(case when course='数学' then score else null end) as score1
,max(case when course='语文' then score else null end) as score2
,max(case when course='英语' then score else null end) as score3
,max(case…
[/Quote]
create table student(studentid int,studentname varchar(20),course varchar(20)
,courseid int,score int)
insert into student select 1,'张三','语文',10,83
union all select 1,'张三','数学',11,99
union all select 1,'张三','英语',12,75
union all select 1,'张三','物理',13,90
union all select 2,'李四','语文',10,91
union all select 2,'李四','数学',11,89
union all select 2,'李四','英语',12,77
union all select 2,'李四','物理',13,69
select studentid, studentname,course,courseid,score as MaxScore from student t where not exists(
select 1 from student where courseid=t.courseid
and score >t.score
)select studentid,max(studentname),'语文'=max(case when course='语文' then score end),
'数学'=max(case when course='数学' then score end),
'英语'=max(case when course='英语' then score end),
'物理'=max(case when course='物理' then score end),
avgscore=avg(score),total=sum(score)
from student
group by studentiddrop table student
/*
studentid studentname course courseid MaxScore
----------- -------------------- -------------------- ----------- -----------
1 张三 数学 11 99
1 张三 物理 13 90
2 李四 语文 10 91
2 李四 英语 12 77(4 行受影响)studentid 语文 数学 英语 物理 avgscore total
----------- -------------------- ----------- ----------- ----------- ----------- ----------- -----------
1 张三 83 99 75 90 86 347
2 李四 91 89 77 69 81 326
(2 行受影响)
*/
select
StudentID, StudentName, a.CourseID, Course, a.Score as 'MaxScore +'
from
STUDENT a
inner join
(select CourseID,MAX(Score) as Score from STUDENT group by CourseID) b
on a.CourseID=b.CourseID
and a.Score=b.Score第二问
select
StudentID, StudentName,
sum(case when Course='数学' then Score end) as 数学,
sum(case when Course='语文' then Score end) as 语文,
sum(case when Course='英语' then Score end) as 英语,
sum(case when Course='地理' then Score end) as 地理,
avg(Score) as AvgScore,
sum(Score) as total
from STUDENT
group by StudentID, StudentName
order by total desc