选课表 userid,classid
1 1
1 2
1 3
2 2
2 6
用户1选了三门课程 用户2选了两门课程课程表 classid,classname
1 语文
2 数学
3 英语
4 物理
5 化学
6 体育
考试成绩表 userid,classid,score,addtime
1 1 95 20130301
1 1 90 20130301
1 3 92 20130304
1 2 78 20130209
现在想查任意一个学生的考试结果。给定任意一个USERID,需要的结果集如下:语文 95 20130301
英语 92 20130304
数学 78 20130209每门课程只要最新的一个考试结果。所有课程的考试成绩需要按考试时间倒序排列。这个SQL该怎么写呢?谢谢大家。
1 1
1 2
1 3
2 2
2 6
用户1选了三门课程 用户2选了两门课程课程表 classid,classname
1 语文
2 数学
3 英语
4 物理
5 化学
6 体育
考试成绩表 userid,classid,score,addtime
1 1 95 20130301
1 1 90 20130301
1 3 92 20130304
1 2 78 20130209
现在想查任意一个学生的考试结果。给定任意一个USERID,需要的结果集如下:语文 95 20130301
英语 92 20130304
数学 78 20130209每门课程只要最新的一个考试结果。所有课程的考试成绩需要按考试时间倒序排列。这个SQL该怎么写呢?谢谢大家。
select classname,score,addtime from 选课表 a join 课程表 b on a.classid=b.classid join
(select userid,classid,score=max(score),addtime=max(addtime) from 考试成绩表 k1 where not exists(select 1 from 考试成绩表 k2 where k1.userid=k2.userid and
k1.classid=k2.classid and k1.addtime<k2.addtime) group by userid,classid) c on a.userid=c.userid and a.classid=c.classid
(select * from 考试成绩表 k1 where not exists(select 1 from 考试成绩表 k2 where k1.userid=k2.userid and k1.classid=k2.classid and k1.addtime<k2.addtime))c on a.userid=c.userid and a.classid=c.classid
create table 课程表(classid int,classname varchar(10))
create table 考试成绩表(userid int,classid int,score float,addtime datetime)
go
insert 选课表
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,2 union all
select 2,6insert 课程表 values(1,'语文')
insert 课程表 values(2,'数学')
insert 课程表 values(3,'英语')
insert 课程表 values(4,'物理')
insert 课程表 values(5,'化学')
insert 课程表 values(6,'体育')insert 考试成绩表
select 1,1,95,'20130301 09:10:00.000' union all
select 1,1,90,'20130301 22:10:00.000' union all
select 1,3,92,'20130304 09:10:00.000' union all
select 1,2,78,'20130209 09:10:00.000'
go
;with cte
as (
select a.userid,c.classname,a.score,a.addtime from 考试成绩表 a, 选课表 b,课程表 c
where a.userid=b.userid and a.classid=c.classid
)
select a.classname,a.score,a.addtime from cte a
where not exists(select 1 from cte where a.userid=userid and a.classname=classname and a.addtime<addtime )
group by a.classname,a.score,a.addtime
order by a.addtime desc
/*
classname score addtime
---- -- ------------------
英语 92 2013-03-04 09:10:00.000
语文 90 2013-03-01 22:10:00.000
数学 78 2013-02-09 09:10:00.000
*/
go
drop table 选课表,课程表,考试成绩表
select classname,score,addtime from scores
left join classes on classes.classid=scores.classid
where score in(select max(addtime) from scores group by classid) and userid=1
group by scores.classid;