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

xiaoxiao2021-03-01  45

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

 

转载请注明原文地址: https://www.6miu.com/read-4123784.html

最新回复(0)