这几天写了几个非简单的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