求一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

现在要求是:查询出 每个学生的每门课程的最近一次考试的分数(即使有学生一门课程没有任何分数,也要查询出来)

[橡胶轮胎行业数字化高总]

很简单不过开会了，不知道回来以后是否能抢分了！

[nolast02]

会散了～
看谁先答

[nolast02]

学科表和学生表怎么一样。。。。。。。。。

[nolast02]

看错了

[橡胶轮胎行业数字化高总]

散会了，还没人抢！

[nolast02]

不知道对不对：

select distinct t1.student_name, t2.subject_name, isnull(t4.student_score,0)
from studentmain t1
left join subjectmain t2
on t1.student_id=t2.student_id
inner join (select t1.* from testschedule t1 where not exists(select 1 from testschedule t2 where t2.test_date>t1.test_date)) t3
on t2.subject_id=t3.subject_id
left join studentscore on t1.student_id=t4.student_id and t3.test_id=t4.test_id

[橡胶轮胎行业数字化高总]

楼上正解了！

[橡胶轮胎行业数字化高总]

今天晚上怎么没人呐？

[dawugui]

现有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

就目前的四个表,不可能得到楼主想要的要求.

[hellowork]

这样试试:
SELECT
a.student_id,
a.student_name,
d.subject_name,
b.student_score,
c.test_date
FROM studentmain as a
LEFT JOIN studentscore as b ON a.student_id = b.student_id
INNER JOIN (select * from testschedule as t where t.test_date =
(select max(test_date) from testschedule where subject_id = t.subject_id)) as c
ON b.test_id = c.test_id
LEFT JOIN subjectmain as d ON c.subject_id = d.subject_id

[mooninday]

先谢谢大家,明天去公司试下,可以用一定给分.

[hellowork]

----创建测试数据
declare @studentmain table(student_id varchar(10),student_name varchar(10))
insert @studentmain
select '00001', 'Mary' union all
select '00002', 'Jack'
declare @studentscore table(student_id varchar(10),test_id int,student_score int)
insert @studentscore
select '00001', 1001, 56 union all
select '00001', 1002, 78 union all
select '00001', 1003, 98
declare @subjectmain table(subject_id int,subject_name varchar(10))
insert @subjectmain
select 11111, 'Maths' union all
select 22222, 'Chinese'
declare @testschedule table(test_id int,subject_id int,test_date varchar(10))
insert @testschedule
select 1001, 11111, '2007/1/1' union all
select 1002, 11111, '2007/2/2' union all
select 1003, 22222, '2007/3/3'

----查询
SELECT
a.student_name,
d.subject_name,
MAX(e.student_score) AS student_score
FROM @studentmain AS a
CROSS JOIN (select * from @testschedule as t where t.test_date =
(select max(test_date) from @testschedule where subject_id = t.subject_id)) AS c
LEFT JOIN @subjectmain AS d ON c.subject_id = d.subject_id
LEFT JOIN @studentscore AS e ON c.test_id = e.test_id and a.student_id = e.student_id
GROUP BY a.student_name,d.subject_name
ORDER BY a.student_name DESC,student_score


/*结果:
student_name subject_name student_score
------------ ------------ -------------
Mary Maths 78
Mary Chinese 98
Jack Maths NULL
Jack Chinese NULL
*/

[hellowork]

这个可以简化一下,不用分组:
----创建测试数据
declare @studentmain table(student_id varchar(10),student_name varchar(10))
insert @studentmain
select '00001', 'Mary' union all
select '00002', 'Jack'
declare @studentscore table(student_id varchar(10),test_id int,student_score int)
insert @studentscore
select '00001', 1001, 56 union all
select '00001', 1002, 78 union all
select '00001', 1003, 98
declare @subjectmain table(subject_id int,subject_name varchar(10))
insert @subjectmain
select 11111, 'Maths' union all
select 22222, 'Chinese'
declare @testschedule table(test_id int,subject_id int,test_date varchar(10))
insert @testschedule
select 1001, 11111, '2007/1/1' union all
select 1002, 11111, '2007/2/2' union all
select 1003, 22222, '2007/3/3'

----查询
SELECT
a.student_name,
d.subject_name,
e.student_score
FROM @studentmain AS a
CROSS JOIN (select * from @testschedule as t where t.test_date =
(select max(test_date) from @testschedule where subject_id = t.subject_id)) AS c
LEFT JOIN @subjectmain AS d ON c.subject_id = d.subject_id
LEFT JOIN @studentscore AS e ON c.test_id = e.test_id and a.student_id = e.student_id
ORDER BY a.student_name DESC,student_score


/*结果:
student_name subject_name student_score
------------ ------------ -------------
Mary Maths 78
Mary Chinese 98
Jack Maths NULL
Jack Chinese NULL
*/

[y__jian]

如果在这段sql文的基础上查处最新的成绩该咋办？比如第一次没及格，后一次及格了，需要出及格的那段分数这段sql还不能解决？？？高手们指点下。。。。。

[mooninday]

谢谢大家。

我的想法是：SQL语句应该会查询出 学生数量X学科数量 条记录。（即使该学生的学科没有分数）

hellowork(一两清风)的貌似不对啊，记录数好像不正确。

nolast02(小明) 的怎么我不能运行啊？我用的是MySQL，是不是有语法错误？

[hellowork]

请楼主为这四个表写些测试数据,然后把应该得到的结果也写出来.

[mooninday]

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
1001 11111 2007/1/1
1002 11111 2007/2/2
1003 22222 2007/3/3


那查询出来的数据可不可能是:
Mary Maths 78
Mary Chinese 98
Jack Maths null
Jack Chinese null