select tstu.f_code+ ':' +tstu.f_strrealname as '学号:姓名',ce.f_examname2 as 考试名称,cast(cbs.f_subject01 as decimal(15,2)) as 语文,cast(cbs.f_subject02 as decimal(15,2)) as 数学,cast(cbs.f_subject03 as decimal(15,2)) as 英语 from C_BasicScore as cbs
left join C_Exam as ce on ce.f_guid=cbs.f_examguid
left join t_student as tstu on tstu.f_guid=cbs.f_stuguid
where cbs.f_classguid in ('8faf1339009c47cfb62aa83c4ed6ed4b')
order by tstu.f_code,ce.f_examname2 C_BasicScore 分数表
C_Exam 考试表
t_student 学生表需求是这样的
有中心端和学校端2个地方可以建立考试
如学校端建立了2011-2012 一年级 第一学期 期中考试 (C_Exam 表中sign字段存为0)
中心端也建立了同一场考试 2011-2012 一年级 第一学期 期中考试(C_Exam 表中sign字段存为1)以上2场考试重复建立
那么就取中心端的那场考试,即C_Exam 表中sign字段为1的考试在C_Exam 中可以判断为同一场考试的字段有F_TermGUID(学期ID ‘2011-2012’)、F_ExamTypeGUID(考试类型 '期中考试')、
F_GradeGUID(年级ID '一年级')
那么就取中心端的那场考试,即C_Exam 表中sign字段为1的考试
ce.f_examname2 as 考试名称,
cast(cbs.f_subject01 as decimal(15,2)) as 语文,
cast(cbs.f_subject02 as decimal(15,2)) as 数学,
cast(cbs.f_subject03 as decimal(15,2)) as 英语
from C_BasicScore as cbs
left join C_Exam as ce on ce.f_guid=cbs.f_examguid
and not exists(select 1 from C_Exam where F_TermGUID=ce.F_TermGUID
and F_ExamTypeGUID=ce.F_ExamTypeGUID and F_GradeGUID=ce.F_GradeGUID and cast([sign] as int) > CAST(ce.[SIGN] as int))
left join t_student as tstu on tstu.f_guid=cbs.f_stuguid
where cbs.f_classguid in ('8faf1339009c47cfb62aa83c4ed6ed4b')
order by tstu.f_code,ce.f_examname2
(select tstu.f_code+ ':' +tstu.f_strrealname as '学号:姓名',
ce.f_examname2 as 考试名称,cast(cbs.f_subject01 as decimal(15,2)) as 语文,
cast(cbs.f_subject02 as decimal(15,2)) as 数学,cast(cbs.f_subject03 as decimal(15,2)) as 英语,
no=row_number() over(partition by ce.F_TermGUID,ce.F_ExamTypeGUID,ce.F_GradeGUID order by ce.sign desc)
from C_BasicScore as cbs
left join C_Exam as ce on ce.f_guid=cbs.f_examguid
left join t_student as tstu on tstu.f_guid=cbs.f_stuguid
where cbs.f_classguid in ('8faf1339009c47cfb62aa83c4ed6ed4b')) t
where no=1
tstu.f_code+ ':' +tstu.f_strrealname as '学号:姓名',
ce.f_examname2 as 考试名称,
cast(cbs.f_subject01 as decimal(15,2)) as 语文,
cast(cbs.f_subject02 as decimal(15,2)) as 数学,
cast(cbs.f_subject03 as decimal(15,2)) as 英语
from C_BasicScore as cbs
left join C_Exam as ce on ce.f_guid=cbs.f_examguid
and cast([sign] as int)=(select max(cast([sign] as int)) from C_Exam where F_TermGUID=ce.F_TermGUID
and F_ExamTypeGUID=ce.F_ExamTypeGUID and F_GradeGUID=ce.F_GradeGUID )
left join t_student as tstu on tstu.f_guid=cbs.f_stuguid
where cbs.f_classguid in ('8faf1339009c47cfb62aa83c4ed6ed4b')
order by tstu.f_code,ce.f_examname2
ce.f_examname2 as 考试名称,
cast(cbs.f_subject01 as decimal(15,2)) as 语文,
cast(cbs.f_subject02 as decimal(15,2)) as 数学,
cast(cbs.f_subject03 as decimal(15,2)) as 英语
from C_BasicScore as cbs
left join C_Exam as ce on ce.f_guid=cbs.f_examguid
left join t_student as tstu on tstu.f_guid=cbs.f_stuguid
where cbs.f_classguid in ('8faf1339009c47cfb62aa83c4ed6ed4b')
and not exists( --增加一个判断条件,当C_Exam表中相同F_TermGUID、F_ExamTypeGUID、F_GradeGUID时,取sign最大的那个(即1)
select 1 from C_Exam
where F_TermGUID=ce.F_TermGUID and F_ExamTypeGUID=ce.F_ExamTypeGUID and F_GradeGUID=ce.F_GradeGUID and [sign]>ce.[sign]
)
order by tstu.f_code,ce.f_examname2
[sign]*1=(select max([sign]*1)
*
from
(select tstu.f_code+ ':' +tstu.f_strrealname as '学号:姓名',
ce.f_examname2 as 考试名称,cast(cbs.f_subject01 as decimal(15,2)) as 语文,
cast(cbs.f_subject02 as decimal(15,2)) as 数学,cast(cbs.f_subject03 as decimal(15,2)) as 英语,
no=row_number() over(partition by ce.F_TermGUID,ce.F_ExamTypeGUID,ce.F_GradeGUID order by ce.sign desc)
from
C_BasicScore as cbs
left join
C_Exam as ce on ce.f_guid=cbs.f_examguid
left join
t_student as tstu on tstu.f_guid=cbs.f_stuguid
where
cbs.f_classguid in ('8faf1339009c47cfb62aa83c4ed6ed4b')) t
where
no=1