sqlserver 经典关联查询语句

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 != '' &amp;&amp;object.lastUpdateTimeEnd != ''">
            AND (
            (EI.LAST_UPDATE_TIME &gt;= #{object.lastUpdateTimeStart}
            AND EI.LAST_UPDATE_TIME &lt;= #{object.lastUpdateTimeEnd})

            OR
            (ECA.LAST_UPDATE_TIME &gt;= #{object.lastUpdateTimeStart}
            AND
            ECA.LAST_UPDATE_TIME &lt;= #{object.lastUpdateTimeEnd})

            OR
            (EM.LAST_UPDATE_TIME &gt;= #{object.lastUpdateTimeStart}
            AND
            EM.LAST_UPDATE_TIME &lt;= #{object.lastUpdateTimeEnd})

            OR
            (ED.LAST_UPDATE_TIME &gt;= #{object.lastUpdateTimeStart}
            AND
            ED.LAST_UPDATE_TIME &lt;= #{object.lastUpdateTimeEnd})

            OR
            (EL.LAST_UPDATE_TIME &gt;= #{object.lastUpdateTimeStart}
            AND
            EL.LAST_UPDATE_TIME &lt;= #{object.lastUpdateTimeEnd})

            OR
            (EP.LAST_UPDATE_TIME &gt;= #{object.lastUpdateTimeStart}
            AND
            EP.LAST_UPDATE_TIME &lt;= #{object.lastUpdateTimeEnd})

            OR
            (ER.LAST_UPDATE_TIME &gt;= #{object.lastUpdateTimeStart}
            AND
            ER.LAST_UPDATE_TIME &lt;= #{object.lastUpdateTimeEnd})

            OR
            (EC.LAST_UPDATE_TIME &gt;= #{object.lastUpdateTimeStart}
            AND
            EC.LAST_UPDATE_TIME &lt;= #{object.lastUpdateTimeEnd})

            )           </when>             <when
            test="object.lastUpdateTimeStart != '' &amp;&amp;object.lastUpdateTimeEnd == ''">
            AND (
            (EI.LAST_UPDATE_TIME &gt;= #{object.lastUpdateTimeStart})

            OR (ECA.LAST_UPDATE_TIME &gt;= #{object.lastUpdateTimeStart})

            OR
            (EM.LAST_UPDATE_TIME &gt;= #{object.lastUpdateTimeStart})

            OR
            (ED.LAST_UPDATE_TIME &gt;= #{object.lastUpdateTimeStart})

            OR
            (EL.LAST_UPDATE_TIME &gt;= #{object.lastUpdateTimeStart})

            OR
            (EP.LAST_UPDATE_TIME &gt;= #{object.lastUpdateTimeStart})

            OR
            (ER.LAST_UPDATE_TIME &gt;= #{object.lastUpdateTimeStart})

            OR
            (EC.LAST_UPDATE_TIME &gt;= #{object.lastUpdateTimeStart})

            )           </when>             <when
            test="object.lastUpdateTimeStart == '' &amp;&amp;object.lastUpdateTimeEnd != ''">
            AND (
            (EI.LAST_UPDATE_TIME &lt;= #{object.lastUpdateTimeEnd})

            OR
            (ECA.LAST_UPDATE_TIME &lt;= #{object.lastUpdateTimeEnd})

            OR
            (EM.LAST_UPDATE_TIME &lt;= #{object.lastUpdateTimeEnd})

            OR
            (ED.LAST_UPDATE_TIME &lt;= #{object.lastUpdateTimeEnd})

            OR
            (EL.LAST_UPDATE_TIME &lt;= #{object.lastUpdateTimeEnd})

            OR
            (EP.LAST_UPDATE_TIME &lt;= #{object.lastUpdateTimeEnd})

            OR
            (ER.LAST_UPDATE_TIME &lt;= #{object.lastUpdateTimeEnd})

            OR
            (EC.LAST_UPDATE_TIME &lt;= #{object.lastUpdateTimeEnd})
            )           </when>         </choose>

ORDER BY EI.EMPLOYEE_NUMBER OFFSET #{start} ROWS FETCH NEXT #{pageSize} ROWS ONLY