求一SQL语句.

[mooninday]

现有4张表:
studentmain:学生表
student_id,student_name
studentscore:分数表
student_id,test_id,student_score
subjectmain:学科表
subject_id,subject_name
testschedule:考试日程表
test_id,subject_id,test_date,test_type (test_type为考试类型,0表示正式考试,1表示补考.)

现在要求是:查询出 每个学生的每门课程的最近一次考试的分数

如现有以下数据:
student_id student_name
00001 Mary
00002 Jack

student_id test_id student_score
00001 1001 56
00001 1002 78
00001 1003 98

subject_id subject_name
11111 Maths
22222 Chinese

test_id subject_id test_date test_type
1001 11111 2007/1/1 0
1002 11111 2007/2/2 1
1003 22222 2007/3/3 0
1003 22222 2007/4/4 1


那查询出来的数据可不可能是:
student_name subject_name student_score test_type
Mary Maths 78 1
Mary Chinese 98 0
Jack Maths null null
Jack Chinese null null

[zengsisan]

你试下这个，，看行不行

select a.student_name c.subject_name b.strdent_score d.test_type
from studentmain a,studentscore b,subjectmain c,testschedule d
where a.student_id=b.student_id
and b.test_id=d.test_id
and d.subject_id=c.subject_id

group by a.student_name
order by a.student_name


试试吧，，应该没什么大问题，，

[zengsisan]

很久没用SQL了，，很多地方都忘记了，所以叫你试下，哈哈，可能很多地方和我现在写的不一样，，比如a.student_name 这样的，可能是student_name.a的，，很久没用了，

[dawugui]

select a.student_name,c.subject_name,b.student_score,d.test_type from studentmain a
left join studentscore b on a.student_id = b.student_id
left join
(
select t1.* from testschedule t1,
(select test_id,subject_id,max(test_date) test_date from testschedule) t2
where t1.test_id = t2.test_id and t1.subject_id = t2.subject_id and t1.test_date = t2.test_date
) d on b.test_id = d.test_id
left join subjectmain on b.subject_id = d.subject_id

[dawugui]

create table studentmain(student_id varchar(10),student_name varchar(10))
insert into studentmain values('00001', 'Mary')
insert into studentmain values('00002', 'Jack')
create table studentscore(student_id varchar(10),test_id varchar(10),student_score int)
insert into studentscore values('00001', '1001', 56)
insert into studentscore values('00001', '1002', 78)
insert into studentscore values('00001', '1003', 98)
create table subjectmain(subject_id varchar(10),subject_name varchar(10))
insert into subjectmain values('11111', 'Maths')
insert into subjectmain values('22222', 'Chinese')
create table testschedule(test_id varchar(10),subject_id varchar(10),test_date datetime,test_type int)
insert into testschedule values('1001', '11111', '2007/1/1', 0)
insert into testschedule values('1002', '11111', '2007/2/2', 1)
insert into testschedule values('1003', '22222', '2007/3/3', 0)
insert into testschedule values('1003', '22222', '2007/4/4', 1)
go

select t1.student_name,t2.subject_name,t1.student_score,t2.test_type
from
(
select a.student_name,b.student_score,b.test_id from studentmain a
left join studentscore b on a.student_id = b.student_id
) t1
left join
(
select a.subject_name,b.test_id,b.test_type from subjectmain a
left join
(
select t1.* from testschedule t1,
(select test_id,subject_id,max(test_date) test_date from testschedule group by test_id,subject_id) t2
where t1.test_id = t2.test_id and t1.subject_id = t2.subject_id and t1.test_date = t2.test_date
) b on a.subject_id = b.subject_id
) t2
on t1.test_id = t2.test_id

--select * from studentmain
--select * from studentscore
--select * from subjectmain
--select * from testschedule
drop table studentmain,studentscore,subjectmain,testschedule

/*
student_name subject_name student_score test_type
------------ ------------ ------------- -----------
Mary Maths 56 0
Mary Maths 78 1
Mary Chinese 98 1
Jack NULL NULL NULL

（所影响的行数为 4 行）
*/

[MakeToday]

select student_name,subject_name,b.score,b.test_type
from studentmain cross join subjectmain
left join (
select studentscore.student_id,subject_id,max(studentscore.test_id) test_id,max(student_score) score,case count(distinct studentscore.test_id) when 1 then '0' else '1' end test_type
from studentscore inner join testschedule on studentscore.test_id=testschedule.test_id
group by studentscore.student_id,subject_id) b
on studentmain.student_id=b.student_id and subjectmain.subject_id=b.subject_id

[MakeToday]

等　一下，分数有点不对

[撸过才知道]

老龟的不行吗?我看可以啊

[MakeToday]

select student_name,subject_name,studentscore.student_score,b.test_type
from studentmain cross join subjectmain
left join (
select studentscore.student_id,subject_id,max(studentscore.test_id) test_id,case count(distinct studentscore.test_id) when 1 then '0' else '1' end test_type
from studentscore inner join testschedule on studentscore.test_id=testschedule.test_id
group by studentscore.student_id,subject_id

) b
on studentmain.student_id=b.student_id and subjectmain.subject_id=b.subject_id
left join studentscore
on studentmain.student_id=studentscore.student_id and b.test_id=studentscore.test_id

[mooninday]

谢谢大家,明天去试下.
被这句SQL搞的烦了.

[kuangjianheng]

学习。。。。。。。。。。。

老乌龟的写法还是有点问题呀。
------------ ------------ ------------- -----------
Mary Maths 56 0
Mary Maths 78 1
Mary Chinese 98 1
Jack NULL NULL NULL

每一门课程应该只有一个成绩，Mary 的 Maths这门成绩出现了两个分数，56分的那条记录应该去掉。

[mooninday]

楼上的说的对
大家看看该怎么改呢?

PS:上面的原始数据有点写错了,现在改一下.

现有4张表:
studentmain:学生表
student_id,student_name
studentscore:分数表
student_id,test_id,student_score
subjectmain:学科表
subject_id,subject_name
testschedule:考试日程表
test_id,subject_id,test_date,test_type (test_type为考试类型,0表示正式考试,1表示补考.)

现在要求是:查询出 每个学生的每门课程的最近一次考试的分数

如现有以下数据:
student_id student_name
00001 Mary
00002 Jack

student_id test_id student_score
00001 1001 56
00001 1002 78
00001 1003 98

subject_id subject_name
11111 Maths
22222 Chinese
33333 English

test_id subject_id test_date test_type
1001 11111 2007/1/1 0
1002 11111 2007/2/2 1
1003 22222 2007/3/3 0
1004 22222 2007/4/4 1


那查询出来的数据可不可能是:
student_name subject_name student_score test_type
Mary Maths 78 1
Mary Chinese 98 0
Jack Maths null null
Jack Chinese null null

[mooninday]

ojuju10(longdchuanren)写的有点问题
比如,像
Jack Maths null null
Jack Chinese null null
这这样的记录都没有啊

[mooninday]

大家帮下忙啦!!!

[ojuju10]

create table studentmain(student_id varchar(10),student_name varchar(10))
insert into studentmain values('00001', 'Mary')
insert into studentmain values('00002', 'Jack')
create table studentscore(student_id varchar(10),test_id varchar(10),student_score int)
insert into studentscore values('00001', '1001', 56)
insert into studentscore values('00001', '1002', 78)
insert into studentscore values('00001', '1003', 98)
create table subjectmain(subject_id varchar(10),subject_name varchar(10))
insert into subjectmain values('11111', 'Maths')
insert into subjectmain values('22222', 'Chinese')
create table testschedule(test_id varchar(10),subject_id varchar(10),test_date datetime,test_type int)
insert into testschedule values('1001', '11111', '2007/1/1', 0)
insert into testschedule values('1002', '11111', '2007/2/2', 1)
insert into testschedule values('1003', '22222', '2007/3/3', 0)
insert into testschedule values('1003', '22222', '2007/4/4', 1)
go

select * from
(select test_id,a.subject_id,test_type,subject_name,test_date from testschedule a inner join subjectmain b
on a.subject_id=b.subject_id) a

right join
(select a.student_id,student_name,test_id,student_score from studentmain a left join studentscore b
on a.student_id=b.student_id) b
on a.test_id=b.test_id

[ojuju10]

你的表结构没有建好，写sql的时候好晕的

[mooninday]

回答楼上的
表结构是一踏糊涂,BS建表的人