SQL求助 谢谢呢~

SQL code

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的考试

SQL code

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

SQL code

select * 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

或重复时取一下，以Sign为bit类型时需要转为int比较

SQL code

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

SQL code

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')
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)

另外,注意 sign 是保留字,最好不要用它来做字段名.