求sql语句，两表连查，子表要选择性的数据
主要是两张表，学生（student），学生的处罚情况（student_punish）,
一个学生可以有0-多个处罚：
student
－－－－－－－－－
student_id, (pk)
student_no,
student_name，
class_id (fk－班级)
student_punish
----------------
PUNISH_ID, (pk)
STUDENT_ID, (fk)
PUNISH_DATE,
PUNISH_type
另外还包括班级，院系表。
class (班级)
--------
class_id,
class_name
department_id (fk--院系)
department （院系）
--------------
department_id
department_name
要求得到学生是否有处罚，如果是，包括最近的（PUNISH_DATE最近）处罚记录：
department_id, class_id, student_id, is_punish， punish_date, punish_type
--------------------------------------
1 1 1 是/否 处罚日期 处罚类型
如果学生没有处罚，处罚日期和类型为空。记录数要与学生数目相同。
谢谢！
[解决办法]
Select c.department_id, a.class_id, a.student_id
, case when p.punish_type is null then '否 ' else '是 ' end ,
isnull(p.punish_date, ' '), isnull(p.punish_type, ' ')
from student a
left join
( select student_punish.studentid, student_punish.punish_date, student_punish.punish_type
from student_punish
inner join (
select student_id, max(punish_date) as maxdate from student_punish
group by student_id) as e
on student_punish.student_id = e.student_id
and student_punish.punish_date = e.maxdate )
as p
on a.studentid = p.student_id
left join class b on a.class_id = b.class_id
left join department c on b.department_id = c.department_id
[解决办法]
呵可.SQL SERVER中,时期和时间变量和毫秒一样是从“1900-01-01 00:00:00.000”开始计算的。所以你即使设为: ' '空值,而显示为1900-01-01 00:00:00.000了.如果不让其显示如此,可以:
............
isnull(p.punish_date,null),isnull(p.punish_type,null)
.................
这时没有处罚的学生就显示为:null值了.可能直观一点儿.
