1:DB2 根据系统时间查询
SELECT * FROM EQU.V_EQUGROUPTEAM WHERE RESOURCEID=603 AND sysdate BETWEEN STARTTIME AND ENDTIME
2:db2更改时间
Update EQU.T_EQ_OPERATIONALPLAN SET IsNew=0,LastOperator='10403', LastOperatetime= to_date('2020-02-28 15:49:58','YYYY-MM-DD HH24:MI:SS') WHERE PlanMonth=to_date('2020-02-01','yyyy-mm-dd')
3:Sql Server 根据系统时间查询
SELECT * FROM EQU.V_EQUGROUPTEAM WHERE RESOURCEID=603 AND GETDATE() BETWEEN STARTTIME AND ENDTIME
4、db2 拼接字符串(nvl()排除空字符串)
SELECT org.NAME,equInfo.proLineName,equInfo.proceName, equInfo.typeModelName, equInfo.normalName, equInfo.INSTALLATIONSIDE, equInfo.EQUIPMENTNAME,
nvl(CONCAT('故障现象:',Imp.FAULTPHENOMENON),'')||nvl(CONCAT(',故障原因::',Imp.FAULTCAUSE),'')||nvl(CONCAT(',故障措施:',Imp.MAINTENANCEMEASURE),'') AS content,
Imp.MTBEGINTIME,Imp.MTENDTIME,Imp.MAINTENANCETIME,person.NAME,MAINTAINER, team.NAME,shift.NAME
FROM equ.T_EQ_EQUIPREPAIRRECORD Imp
LEFT JOIN EQU.V_ORGANIZE_TREE as org on Imp.ORGID=org.ID
LEFT JOIN EQU.V_TEAM as team on team.ID=Imp.SHIFTCLASSCODE
LEFT JOIN EQU.V_SHIFT as shift on shift.ID=Imp.CLASSTIMECODE
LEFT JOIN EQU.V_PERSON as person on person.ID=Imp.CREATER
LEFT JOIN( SELECT
ment.EQUIPMENTNAME ,
ment.EQ_EQUIPMENTID ,
proLine.PHYSICSSECTIONNAME AS proLineName,
Duan.PHYSICSSECTIONNAME AS proceName,
tmodel.EQUIPTYPEMODELNAME AS typeModelName,
tmodel1.EQUIPTYPEMODELNAME AS normalName,
ment.INSTALLATIONSIDE
FROM EQU.T_EQ_EQUIPMENT ment
LEFT JOIN EQU.V_PROCESSROUTING proLine ON ment.PRODUCTIONLINEID=proLine.PB_EQ_PHYSICSSECTIONID
LEFT JOIN EQU.V_PRODUCTIONRESOURCE Duan ON ment.SECTIONID=Duan.PB_EQ_PHYSICSSECTIONID
LEFT JOIN EQU.T_EQ_EQUIPTYPEMODEL tmodel ON ment.EQUIPTYPEDETAILID=tmodel.EQ_EQUIPTYPEMODELID
LEFT JOIN EQU.T_EQ_EQUIPTYPEMODEL tmodel1 ON ment.EQ_EQUIPTYPEMODELID=tmodel1.EQ_EQUIPTYPEMODELID
) as equInfo on Imp.EQ_EQUIPMENTID=equInfo.EQ_EQUIPMENTID