sqlserver关联查询:
SELECT
EI.EMPLOYEE_NAME AS employeeName, EI.EMPLOYEE_NUMBER AS employeeNumber,
EI.E_MAIL AS email,
EI.EMPLOYEE_STATUS AS employeeStatus, EI.ISPEOPLEMANAGER AS isPeopleManager, EI.HIRE_DATE AS hireDate,
EI.SEX AS sex,
EI.OFFICE_LOCATION AS officeLocationName, EC.EMPLOYEE_FUNCTION AS costType, ECA.LEGAL_ENTITY_ID AS leEntityId, EM.MANAGER_NUMBER AS managerNumber, ED.DEPARTMENT_NAME AS departmentName, ED.DEPARTMENT_ID AS departmentId,
EL.LOCATION AS location,
EP.GRADE_NAME AS gradeName,
EP.POSITION_CN_NAME AS positionCNname, ER.RESIGN_DATE AS resignDate ,
( SELECT MAX(LastUpdateTIME) FROM (
VALUES (
EI.LAST_UPDATE_TIME),
( ECA.LAST_UPDATE_TIME),
( EM.LAST_UPDATE_TIME),
( ED.LAST_UPDATE_TIME),
( EL.LAST_UPDATE_TIME),
( EP.LAST_UPDATE_TIME), (ER.LAST_UPDATE_TIME), (EC.LAST_UPDATE_TIME)
) AS UpdateDate ( LastUpdateTIME ) ) AS lastUpdateTime FROM
V_Mule_Employee_Info EI
LEFT JOIN V_Mule_Emp_Con_all ECA ON EI.EMPLOYEE_NUMBER = ECA.EMPLOYEE_NUMBER collate Chinese_PRC_CI_AS AND IS_CURRENT=’1’
LEFT JOIN V_Mule_Employee_Manager EM ON EI.EMPLOYEE_NUMBER = EM.EMPLOYEE_NUMBER collate Chinese_PRC_CI_AS
LEFT JOIN V_Mule_Employee_Department ED ON EI.EMPLOYEE_NUMBER = ED.EMPLOYEE_NUMBER collate Chinese_PRC_CI_AS
LEFT JOIN V_Mule_Employee_Location EL ON EI.EMPLOYEE_NUMBER = EL.EMPLOYEE_NUMBER collate Chinese_PRC_CI_AS
LEFT JOIN V_Mule_Employee_Position EP ON EI.EMPLOYEE_NUMBER = EP.EMPLOYEE_NUMBER collate Chinese_PRC_CI_AS
LEFT JOIN V_Mule_Employee_Resign ER ON EI.EMPLOYEE_NUMBER = ER.EMPLOYEE_NUMBER collate Chinese_PRC_CI_AS
LEFT JOIN (SELECT DISTINCT EMPLOYEE_NUMBER,EMPLOYEE_FUNCTION,LAST_UPDATE_TIME FROM V_Mule_Employee_CCC WHERE IS_CURRENT = '1') EC ON EI.EMPLOYEE_NUMBER = EC.EMPLOYEE_NUMBER collate Chinese_PRC_CI_AS
WHERE 1=1
<choose> <when
test="object.lastUpdateTimeStart != '' &&object.lastUpdateTimeEnd != ''">
AND (
(EI.LAST_UPDATE_TIME >= #{object.lastUpdateTimeStart}
AND EI.LAST_UPDATE_TIME <= #{object.lastUpdateTimeEnd})
OR
(ECA.LAST_UPDATE_TIME >= #{object.lastUpdateTimeStart}
AND
ECA.LAST_UPDATE_TIME <= #{object.lastUpdateTimeEnd})
OR
(EM.LAST_UPDATE_TIME >= #{object.lastUpdateTimeStart}
AND
EM.LAST_UPDATE_TIME <= #{object.lastUpdateTimeEnd})
OR
(ED.LAST_UPDATE_TIME >= #{object.lastUpdateTimeStart}
AND
ED.LAST_UPDATE_TIME <= #{object.lastUpdateTimeEnd})
OR
(EL.LAST_UPDATE_TIME >= #{object.lastUpdateTimeStart}
AND
EL.LAST_UPDATE_TIME <= #{object.lastUpdateTimeEnd})
OR
(EP.LAST_UPDATE_TIME >= #{object.lastUpdateTimeStart}
AND
EP.LAST_UPDATE_TIME <= #{object.lastUpdateTimeEnd})
OR
(ER.LAST_UPDATE_TIME >= #{object.lastUpdateTimeStart}
AND
ER.LAST_UPDATE_TIME <= #{object.lastUpdateTimeEnd})
OR
(EC.LAST_UPDATE_TIME >= #{object.lastUpdateTimeStart}
AND
EC.LAST_UPDATE_TIME <= #{object.lastUpdateTimeEnd})
) </when> <when
test="object.lastUpdateTimeStart != '' &&object.lastUpdateTimeEnd == ''">
AND (
(EI.LAST_UPDATE_TIME >= #{object.lastUpdateTimeStart})
OR (ECA.LAST_UPDATE_TIME >= #{object.lastUpdateTimeStart})
OR
(EM.LAST_UPDATE_TIME >= #{object.lastUpdateTimeStart})
OR
(ED.LAST_UPDATE_TIME >= #{object.lastUpdateTimeStart})
OR
(EL.LAST_UPDATE_TIME >= #{object.lastUpdateTimeStart})
OR
(EP.LAST_UPDATE_TIME >= #{object.lastUpdateTimeStart})
OR
(ER.LAST_UPDATE_TIME >= #{object.lastUpdateTimeStart})
OR
(EC.LAST_UPDATE_TIME >= #{object.lastUpdateTimeStart})
) </when> <when
test="object.lastUpdateTimeStart == '' &&object.lastUpdateTimeEnd != ''">
AND (
(EI.LAST_UPDATE_TIME <= #{object.lastUpdateTimeEnd})
OR
(ECA.LAST_UPDATE_TIME <= #{object.lastUpdateTimeEnd})
OR
(EM.LAST_UPDATE_TIME <= #{object.lastUpdateTimeEnd})
OR
(ED.LAST_UPDATE_TIME <= #{object.lastUpdateTimeEnd})
OR
(EL.LAST_UPDATE_TIME <= #{object.lastUpdateTimeEnd})
OR
(EP.LAST_UPDATE_TIME <= #{object.lastUpdateTimeEnd})
OR
(ER.LAST_UPDATE_TIME <= #{object.lastUpdateTimeEnd})
OR
(EC.LAST_UPDATE_TIME <= #{object.lastUpdateTimeEnd})
) </when> </choose>
ORDER BY EI.EMPLOYEE_NUMBER OFFSET #{start} ROWS FETCH NEXT #{pageSize} ROWS ONLY