公司项目要求做出排行榜，根据六组数据依次排行，关联多表，SQL记录下来方便日后查看

" ?>

<!-- 共用条件时间筛选条件 -->
<sql id="reward\_time">
    <if test="startTime != null">AND payment.RECEIVED\_PAYMENTS\_TIME &gt; #{startTime}</if>
    <if test="endTime != null">AND payment.RECEIVED\_PAYMENTS\_TIME &lt; #{endTime}</if>
</sql>
<sql id="entery\_time">
    <if test="startTime != null">AND placement.ENTER\_OPERATOR\_TIME &gt; #{startTime}</if>
    <if test="endTime != null">AND placement.ENTER\_OPERATOR\_TIME &lt; #{endTime}</if>
</sql>
<sql id="pament\_time">
    <if test="startTime != null">AND placement.CREATE\_TIME &gt; #{startTime}</if>
    <if test="endTime != null">AND placement.CREATE\_TIME &lt; #{endTime}</if>
</sql>
<sql id="first\_interview\_time">
    <if test="startTime != null">AND interview.CREATE\_TIME &gt; #{startTime}</if>
    <if test="endTime != null">AND interview.CREATE\_TIME &lt; #{endTime}</if>
</sql>
<sql id="interview\_time">
    <if test="startTime != null">AND recommendStream.CREATE\_TIME &gt; #{startTime}</if>
    <if test="endTime != null">AND recommendStream.CREATE\_TIME &lt; #{endTime}</if>
</sql>
<sql id="placement\_time">
    <if test="startTime != null">AND placement.CREATE\_TIME &gt; #{startTime}</if>
    <if test="endTime != null">AND placement.CREATE\_TIME &lt; #{endTime}</if>
</sql>
<sql id="signed\_time">
    <if test="startTime != null">AND company.SIGN\_TIME &gt; #{startTime}</if>
    <if test="endTime != null">AND company.SIGN\_TIME &lt; #{endTime}</if>
</sql>

<!-- 公共排序条件 -->
<sql id="commond\_order\_by">
    ">
        ">
            ORDER BY reward.rewardTotal DESC, pament.pamentTotal DESC, entery.enteryTotal DESC,
            placement.offerCountTotal DESC,interviewed.interviewedTotal DESC,signed.signTotal DESC
        </if>
        ">
            ORDER BY pament.pamentTotal DESC, placement.offerCountTotal DESC, interviewed.interviewedTotal DESC,
            reward.rewardTotal DESC, signed.signTotal DESC, entery.enteryTotal DESC
        </if>
        ">
            ORDER BY entery.enteryTotal DESC, pament.pamentTotal DESC, placement.offerCountTotal DESC,
            interviewed.interviewedTotal DESC, reward.rewardTotal DESC, signed.signTotal DESC
        </if>
        ">
            ORDER BY interviewed.interviewedTotal DESC, entery.enteryTotal DESC, pament.pamentTotal DESC,
            placement.offerCountTotal DESC,reward.rewardTotal DESC, signed.signTotal DESC
        </if>
        <!-- 按组排行的条件 -->
        ">
            ORDER BY reward.rewardTotal DESC, pament.pamentTotal DESC, signed.signTotal DESC,
            entery.enteryTotal DESC,placement.offerCountTotal DESC,interviewed.interviewedTotal DESC
        </if>
    </if>
</sql>

<!-- AM个人排行榜（不包括签约王） -->
<select id="selectBySalesAdmin" resultType="com.lieni.tuimu.dim.model.ExtraRankingList">
    SELECT
        staff.\`ID\` AS 'amId',
        staff.\`NAME\` AS 'amName',
        reward.rewardTotal AS 'rewardTotal', -- 已回款总数
        placement.offerCountTotal AS 'offerCountTotal', -- offer数量
        interviewed.interviewedTotal AS 'interviewedTotal',-- 到面数
        entery.enteryTotal AS 'enteryTotal',-- 入职数
        pament.pamentTotal AS 'pamentTotal', -- 佣金数
        signed.signTotal AS 'signTotal' -- 签约数
    FROM
        T\_DIM\_TUIMU\_STAFF staff
    LEFT JOIN (
        SELECT
            IFNULL()),) AS rewardTotal,
            -- 已回款总数
            payment.HUNTER\_ADMIN\_ID AS HUNTER\_ADMIN\_ID
        FROM
          T\_DIM\_TUIMU\_PAYMENT payment
        LEFT JOIN T\_DIM\_TUIMU\_COMPANY company ON payment.COMPANY\_ID = company.ID
        WHERE

            ">
                AND company.TYPE = #{type}
            </if>
            <include refid="reward\_time" />
        GROUP BY
            payment.HUNTER\_ADMIN\_ID) reward ON staff.ID = reward.HUNTER\_ADMIN\_ID
    LEFT JOIN (
        SELECT
            ) AS enteryTotal,
            -- 入职数
            recommendStream.HUNTER\_ADMIN\_ID AS HUNTER\_ADMIN\_ID
        FROM
            T\_DIM\_TUIMU\_RECOMMEND\_STREAM recommendStream
        LEFT JOIN T\_DIM\_TUIMU\_PLACEMENT placement ON recommendStream.RECOMMEND\_ID = placement.RECOMMEND\_ID
        LEFT JOIN T\_DIM\_TUIMU\_COMPANY company ON placement.COMPANY\_ID = company.ID
        WHERE
                recommendStream.TARGET\_MAIN\_STATUS 

            ">
                AND company.TYPE = #{type}
            </if>
            <include refid="entery\_time" />
        GROUP BY
            recommendStream.HUNTER\_ADMIN\_ID) entery ON staff.ID = entery.HUNTER\_ADMIN\_ID
    LEFT JOIN (
        SELECT
            SUM(pament.pamentTotal) AS pamentTotal,
            pament.HUNTER\_ADMIN\_ID AS HUNTER\_ADMIN\_ID
        FROM
        -- 到面产品
        (
            SELECT
                IFNULL()),) AS pamentTotal,
                -- offer加推荐的总佣金
                recommendStream.HUNTER\_ADMIN\_ID AS HUNTER\_ADMIN\_ID
            FROM
                T\_DIM\_TUIMU\_RECOMMEND recommend
            LEFT JOIN T\_DIM\_TUIMU\_RECOMMEND\_STREAM recommendStream ON recommendStream.RECOMMEND\_ID = recommend.ID
            LEFT JOIN T\_DIM\_TUIMU\_INTERVIEW interview ON recommendStream.RECOMMEND\_ID = interview.RECOMMEND\_ID
            LEFT JOIN T\_DIM\_TUIMU\_COMPANY company ON recommend.COMPANY\_ID = company.ID
            WHERE

                ">
                    AND company.TYPE = #{type}
                </if>
                <include refid="first\_interview\_time" />
            GROUP BY
                recommendStream.HUNTER\_ADMIN\_ID
            UNION ALL
            -- 非到面产品
            SELECT
                IFNULL()),) AS pamentTotal,
                recommendStream.HUNTER\_ADMIN\_ID AS HUNTER\_ADMIN\_ID
            FROM
                T\_DIM\_TUIMU\_PLACEMENT placement
            LEFT JOIN T\_DIM\_TUIMU\_RECOMMEND\_STREAM recommendStream ON recommendStream.RECOMMEND\_ID = placement.RECOMMEND\_ID
            LEFT JOIN T\_DIM\_TUIMU\_RECOMMEND recommend ON recommend.ID = recommendStream.RECOMMEND\_ID
            LEFT JOIN T\_DIM\_TUIMU\_COMPANY company ON recommend.COMPANY\_ID = company.ID
            WHERE

                ">
                    AND company.TYPE = #{type}
                </if>
                <include refid="pament\_time" />
            GROUP BY
                recommendStream.HUNTER\_ADMIN\_ID
        ) pament
        GROUP BY
            pament.HUNTER\_ADMIN\_ID) pament ON staff.ID = pament.HUNTER\_ADMIN\_ID
    LEFT JOIN (
        SELECT
            ) AS interviewedTotal,
            recommendStream.HUNTER\_ADMIN\_ID AS HUNTER\_ADMIN\_ID
        FROM
            T\_DIM\_TUIMU\_COMPANY company
        LEFT JOIN T\_DIM\_TUIMU\_RECOMMEND\_STREAM recommendStream ON recommendStream.COMPANY\_ID = company.ID
        WHERE
            (recommendStream.SOURCE\_MAIN\_STATUS 

            ,))
          OR
            (recommendStream.SOURCE\_MAIN\_STATUS 

            )
            ">
                AND company.TYPE = #{type}
            </if>
            <include refid="interview\_time" />
        GROUP BY
            recommendStream.HUNTER\_ADMIN\_ID) interviewed ON staff.ID = interviewed.HUNTER\_ADMIN\_ID
    LEFT JOIN (
        SELECT
            ) AS offerCountTotal,
            -- offer 数量
            recommendStream.HUNTER\_ADMIN\_ID AS HUNTER\_ADMIN\_ID
        FROM
            T\_DIM\_TUIMU\_RECOMMEND\_STREAM recommendStream
        LEFT JOIN T\_DIM\_TUIMU\_PLACEMENT placement ON recommendStream.RECOMMEND\_ID = placement.RECOMMEND\_ID
        LEFT JOIN T\_DIM\_TUIMU\_COMPANY company ON placement.COMPANY\_ID = company.ID
        WHERE
                recommendStream.TARGET\_MAIN\_STATUS 

            ">
                AND company.TYPE = #{type}
            </if>
                <include refid="placement\_time" />
        GROUP BY
            recommendStream.HUNTER\_ADMIN\_ID) placement ON staff.ID = placement.HUNTER\_ADMIN\_ID
    LEFT JOIN (
        SELECT
            ) AS signTotal,
            customerStream.SERVICE\_ADMIN AS SERVICE\_ADMIN
        FROM
            T\_DIM\_TUIMU\_CUSTOMER\_STREAM customerStream
        LEFT JOIN T\_DIM\_TUIMU\_COMPANY company ON customerStream.CUSTOMER\_ID = company.ID
        WHERE
            customerStream.SOURCE\_SIGNED 

        ">
            AND company.TYPE = #{type}
        </if>
            <include refid="signed\_time" />
        GROUP BY
            customerStream.SERVICE\_ADMIN) signed ON staff.ID = signed.SERVICE\_ADMIN
    WHERE
    FIND\_IN\_SET(,staff.DUTIES)
    ">
        AND staff.ROLE\_ID IN
        <foreach collection="roles" item="role" open="(" close=")" separator=",">
            #{role}
        </foreach>
    </if>

    ">
        AND staff.\`ID\` IN
        <foreach collection="salesAdminIds" item="userId" open="(" close=")" separator=",">
            #{userId}
        </foreach>
    </if>
    <include refid="commond\_order\_by"/>
    LIMIT , #{maxRows};
</select>

<!-- 签约王 -->
<select id="selectSignedContract" resultType="com.lieni.tuimu.dim.model.ExtraRankingList">
    SELECT
        signed.signTotal AS signTotal,
        staff.ID AS amId,
        staff.\`NAME\` AS amName,
        signed.signTime AS signTime
    FROM
      T\_DIM\_TUIMU\_STAFF staff
    LEFT JOIN (
        SELECT
            ) AS signTotal,
            customerStream.SALES\_ADMIN AS SALES\_ADMIN,
            MAX(company.SIGN\_TIME) AS signTime
        FROM
            T\_DIM\_TUIMU\_COMPANY company
        LEFT JOIN T\_DIM\_TUIMU\_CUSTOMER\_STREAM customerStream ON customerStream.CUSTOMER\_ID = company.ID
        WHERE
            customerStream.SOURCE\_SIGNED 

            <include refid="signed\_time" />
        GROUP BY
            customerStream.SALES\_ADMIN
        ) signed ON signed.SALES\_ADMIN = staff.ID
     WHERE
    FIND\_IN\_SET(,staff.DUTIES)
    , , , , , )
    ">
        AND staff.\`ID\` IN
        <foreach collection="salesAdminIds" item="userId" open="(" close=")" separator=",">
            #{userId}
        </foreach>
    </if>
    ORDER BY signed.signTotal,signed.signTime
    LIMIT , #{maxRows};
</select>

<!-- 指定组排行榜 -->
<select id="selectByOrganization" resultType="com.lieni.tuimu.dim.model.ExtraRankingList">
    SELECT
        organization.\`ID\` AS 'groupId',
        organization.\`NAME\` AS 'groupName',
        IFNULL(reward.rewardTotal, ) AS 'rewardTotal',
        IFNULL(pament.pamentTotal,) AS 'pamentTotal',
        IFNULL(signed.signTotal, ) AS 'signTotal',
        IFNULL(entery.enteryTotal, ) AS 'enteryTotal',
        IFNULL(placement.offerCountTotal, ) AS 'offerCountTotal',
        IFNULL(interviewed.interviewedTotal, ) AS 'interviewedTotal'
    FROM
      \`T\_DIM\_TUIMU\_ORGANIZATION\` organization
    LEFT JOIN (
       <!--  按组的已回款总数 -->
        SELECT
            IFNULL()),) AS 'rewardTotal',
            ">
                CASE
                <foreach collection="organizations" item="organization">
                    ) IN
                    <foreach collection="organization.children" item="child" open="(" close=")" separator=",">
                        #{child}
                    </foreach>
                    THEN #{organization.parentId}
                </foreach>

                END AS 'organizationId'
            </if>
            ">
                SUBSTRING\_INDEX(payment.HUNTER\_ADMIN\_ORGANIZATION, ) AS 'organizationId'
            </if>
        FROM
          T\_DIM\_TUIMU\_PAYMENT payment
        LEFT JOIN T\_DIM\_TUIMU\_COMPANY company ON payment.COMPANY\_ID = company.ID
        WHERE

            ">
                AND company.TYPE = #{type}
            </if>
            <include refid = "reward\_time" />
        GROUP BY
            payment.HUNTER\_ADMIN\_ORGANIZATION
            ) reward ON organization.\`ID\` = reward.\`organizationId\`
    LEFT JOIN (
        <!--  offer加推荐的总佣金 -->
        SELECT
            )) AS pamentTotal,
            pament.organizationId
        FROM
            -- 到面产品
            (
            SELECT
                recommend.REWARD\_ALL AS rewardAll,
                -- offer加推荐的总佣金
                ">
                    CASE
                    <foreach collection="organizations" item="organization">
                        ) IN
                        <foreach collection="organization.children" item="child" open="(" close=")" separator=",">
                            #{child}
                        </foreach>
                        THEN #{organization.parentId}
                    </foreach>

                    END AS 'organizationId'
                </if>
                ">
                    SUBSTRING\_INDEX(recommendStream.HUNTER\_ADMIN\_ORGANIZATION, ) AS 'organizationId'
                </if>
            FROM
                T\_DIM\_TUIMU\_RECOMMEND recommend
            LEFT JOIN T\_DIM\_TUIMU\_RECOMMEND\_STREAM recommendStream ON recommendStream.RECOMMEND\_ID = recommend.ID
            LEFT JOIN T\_DIM\_TUIMU\_INTERVIEW interview ON recommendStream.RECOMMEND\_ID = interview.RECOMMEND\_ID
            LEFT JOIN T\_DIM\_TUIMU\_COMPANY company ON recommend.COMPANY\_ID = company.ID
            WHERE

                ">
                    AND company.TYPE = #{type}
                </if>
                <include refid="first\_interview\_time" />
            UNION ALL
            -- 非到面产品
            SELECT
                recommend.REWARD\_ALL AS rewardAll,
                ">
                    CASE
                    <foreach collection="organizations" item="organization">
                        ) IN
                        <foreach collection="organization.children" item="child" open="(" close=")" separator=",">
                            #{child}
                        </foreach>
                        THEN #{organization.parentId}
                    </foreach>

                    END AS 'organizationId'
                </if>
                ">
                    SUBSTRING\_INDEX(recommendStream.HUNTER\_ADMIN\_ORGANIZATION, ) AS 'organizationId'
                </if>
            FROM
                T\_DIM\_TUIMU\_PLACEMENT placement
            LEFT JOIN T\_DIM\_TUIMU\_RECOMMEND\_STREAM recommendStream ON recommendStream.RECOMMEND\_ID = placement.RECOMMEND\_ID
            LEFT JOIN T\_DIM\_TUIMU\_RECOMMEND recommend ON recommend.ID = recommendStream.RECOMMEND\_ID
            LEFT JOIN T\_DIM\_TUIMU\_COMPANY company ON recommend.COMPANY\_ID = company.ID
            WHERE

                ">
                    AND company.TYPE = #{type}
                </if>
                <include refid="pament\_time" />
            ) pament
            GROUP BY
            pament.organizationId
            ) pament ON organization.ID = pament.organizationId
    LEFT JOIN (
        <!--  签约数 -->
        SELECT
            COUNT(DISTINCT sign\_temp.companyId) AS signTotal,
            sign\_temp.organizationId
        FROM
            (
            SELECT
                company.ID AS companyId,
                ">
                    CASE
                    <foreach collection="organizations" item="organization">
                        ) IN
                        <foreach collection="organization.children" item="child" open="(" close=")" separator=",">
                            #{child}
                        </foreach>
                        THEN #{organization.parentId}
                    </foreach>

                    END AS 'organizationId'
                </if>
                ">
                    SUBSTRING\_INDEX(customerStream.SALES\_ADMIN\_ORGANIZATION, ) AS 'organizationId'
                </if>
            FROM
              T\_DIM\_TUIMU\_CUSTOMER\_STREAM customerStream
            LEFT JOIN T\_DIM\_TUIMU\_COMPANY company ON customerStream.CUSTOMER\_ID = company.ID
            WHERE
                customerStream.SOURCE\_SIGNED 

                ">
                    AND company.TYPE = #{type}
                </if>
                <include refid = "signed\_time" />
            ) sign\_temp
        GROUP BY
            sign\_temp.organizationId
            ) signed ON organization.ID = signed.organizationId
    LEFT JOIN (
         <!--  入职数 -->
        SELECT
            COUNT(DISTINCT entery\_temp.placementId) AS enteryTotal,
            entery\_temp.organizationId
        FROM
            (
            SELECT
                placement.ID AS placementId,
                ">
                    CASE
                    <foreach collection="organizations" item="organization">
                        ) IN
                        <foreach collection="organization.children" item="child" open="(" close=")" separator=",">
                            #{child}
                        </foreach>
                        THEN #{organization.parentId}
                    </foreach>

                    END AS 'organizationId'
                </if>
                ">
                    SUBSTRING\_INDEX(recommendStream.HUNTER\_ADMIN\_ORGANIZATION, ) AS 'organizationId'
                </if>
            FROM
              T\_DIM\_TUIMU\_RECOMMEND\_STREAM recommendStream
            LEFT JOIN T\_DIM\_TUIMU\_PLACEMENT placement ON recommendStream.RECOMMEND\_ID = placement.RECOMMEND\_ID
            LEFT JOIN T\_DIM\_TUIMU\_COMPANY company ON placement.COMPANY\_ID = company.ID

            WHERE
                recommendStream.SOURCE\_MAIN\_STATUS 

                ">
                    AND company.TYPE = #{type}
                </if>
                <include refid="entery\_time" />
            ) entery\_temp
        GROUP BY
            entery\_temp.organizationId
            ) entery ON organization.ID = entery.organizationId
    LEFT JOIN (
        <!--  offer数 -->
        SELECT
            COUNT(DISTINCT offer\_temp.placementId) AS offerCountTotal,
            offer\_temp.organizationId
        FROM
            (
            SELECT
                placement.ID AS placementId,
                ">
                    CASE
                    <foreach collection="organizations" item="organization">
                        ) IN
                        <foreach collection="organization.children" item="child" open="(" close=")" separator=",">
                            #{child}
                        </foreach>
                        THEN #{organization.parentId}
                    </foreach>

                    END AS 'organizationId'
                </if>
                ">
                    SUBSTRING\_INDEX(recommendStream.HUNTER\_ADMIN\_ORGANIZATION, ) AS 'organizationId'
                </if>
            FROM
              T\_DIM\_TUIMU\_RECOMMEND\_STREAM recommendStream
            LEFT JOIN T\_DIM\_TUIMU\_PLACEMENT placement ON recommendStream.RECOMMEND\_ID = placement.RECOMMEND\_ID
            LEFT JOIN T\_DIM\_TUIMU\_COMPANY company ON placement.COMPANY\_ID = company.ID
            WHERE
                 TARGET\_MAIN\_STATUS 

                ">
                    AND company.TYPE = #{type}
                </if>
                <include refid="placement\_time" />
            ) offer\_temp
        GROUP BY
            offer\_temp.organizationId
            ) placement ON organization.ID = placement.organizationId
    LEFT JOIN (
        <!--  到面数 -->
        SELECT
            COUNT(DISTINCT interview\_temp.interviewedId) AS interviewedTotal,
            interview\_temp.organizationId
        FROM
            (
            SELECT
                    recommendStream.RECOMMEND\_ID AS interviewedId,
                ">
                    CASE
                    <foreach collection="organizations" item="organization">
                        ) IN
                        <foreach collection="organization.children" item="child" open="(" close=")" separator=",">
                            #{child}
                        </foreach>
                        THEN #{organization.parentId}
                    </foreach>

                    END AS 'organizationId'
                </if>
                ">
                    SUBSTRING\_INDEX(recommendStream.HUNTER\_ADMIN\_ORGANIZATION, ) AS 'organizationId'
                </if>
                    FROM
                      T\_DIM\_TUIMU\_COMPANY company
                    LEFT JOIN T\_DIM\_TUIMU\_RECOMMEND\_STREAM recommendStream ON recommendStream.COMPANY\_ID = company.ID
                    WHERE
                        (recommendStream.SOURCE\_MAIN\_STATUS 

                        ,))
                    OR
                        (recommendStream.SOURCE\_MAIN\_STATUS 

                        )
                ">
                    AND company.TYPE = #{type}
                </if>
                <include refid="interview\_time" />
            ) interview\_temp
        GROUP BY
            interview\_temp.organizationId
            ) interviewed ON organization.ID = interviewed.organizationId
        <where>

            ">
                AND organization.\`ID\` IN
                <foreach collection="organizations" item="organization" open="(" close=")" separator=",">
                    #{organization.parentId}
                </foreach>
            </if>
        </where>
        <include refid="commond\_order\_by"/>
        LIMIT , #{maxRows};
</select>