金蝶BIM开发 复杂 SQL更新语句

这几天写了几个非简单的SQL查询语句，记录下：

/*dialect*/update CT_EC_ServiceStaffTable set CFHandInjuryDate = SS.lastenddate, 
CFContrcatStatus = case when (SS.firstenddate>=getdate()) then '01' when 
(SS.lastenddate<getdate()) then '02' else '03' end from (select XX.fid as 
fid,max(X.planenddate)as lastenddate,min(X.planenddate)as firstenddate from 
CT_EC_ServiceStaffTable XX inner join (select b.CFLabourPersonID as fid,c.FEndDate as 
planenddate from T_EC_Roster a left join T_EC_RosterEntry b on a.fid = b.fbillid left join 
T_EC_Project c on c.fid = a.FProjectID union all select b.CFHeadmanNameID as fid,c.FEndDate 
as planedndate from T_EC_Roster a left join T_EC_ContructUnit b on b.fid = a.FWorkTeam left 
join T_EC_Project c on c.fid = a.FProjectID)X on X.fid = XX.fid group by xx.fid)SS  where 
CT_EC_ServiceStaffTable.fid = SS.fid

更新语句：

简单：

update 表名字 set 字段名 = '值'  where 条件

复杂点：

/*dialect*/ update CT_EC_ServiceStaffTable set CFHandInjuryDate = SS.lastenddate from 

(我这里是一个子查询，可以看出一张表

)SS

where CT_EC_ServiceStaffTable.fid = SS.fid

再复杂点

见第一个sql

主要表现一个case when语句

set CFContrcatStatus = case when (SS.firstenddate>=getdate()) then '01'
when (SS.lastenddate<getdate()) then '02'
else '03'
end