复杂SQL示例 （排行榜需求）

最新推荐文章于 2024-12-07 23:52:09 发布
最新推荐文章于 2024-12-07 23:52:09 发布
阅读量184 收藏 1
点赞数
CC 4.0 BY-SA版权
文章标签： java
原文链接：http://www.cnblogs.com/LTmei/p/8932338.html
本文介绍了一个复杂的MyBatis多表关联查询案例，通过使用动态SQL实现不同业务场景下的数据汇总，如个人排行榜、组织排行榜等，并可根据时间范围和其他条件进行筛选。 
公司项目要求做出排行榜，根据六组数据依次排行，关联多表，SQL记录下来方便日后查看
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.lieni.tuimu.dim.ranking.mapper.DimTuimuRankingListMapper">

    <!-- 共用条件时间筛选条件 -->
    <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">
        <if test="sortType != null and sortType > 0">
            <if test="sortType == 1">
                ORDER BY reward.rewardTotal DESC, pament.pamentTotal DESC, entery.enteryTotal DESC,
                placement.offerCountTotal DESC,interviewed.interviewedTotal DESC,signed.signTotal DESC
            </if>
            <if test="sortType == 2">
                ORDER BY pament.pamentTotal DESC, placement.offerCountTotal DESC, interviewed.interviewedTotal DESC,
                reward.rewardTotal DESC, signed.signTotal DESC, entery.enteryTotal DESC
            </if>
            <if test="sortType == 3">
                ORDER BY entery.enteryTotal DESC, pament.pamentTotal DESC, placement.offerCountTotal DESC,
                interviewed.interviewedTotal DESC, reward.rewardTotal DESC, signed.signTotal DESC
            </if>
            <if test="sortType == 4">
                ORDER BY interviewed.interviewedTotal DESC, entery.enteryTotal DESC, pament.pamentTotal DESC,
                placement.offerCountTotal DESC,reward.rewardTotal DESC, signed.signTotal DESC
            </if>
            <!-- 按组排行的条件 -->
            <if test="sortType == 6">
                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(SUM(IFNULL(payment.RECEIVED_PAYMENTS_AMOUNT,0)),0) 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
                1 = 1
                <if test="type != null and type > 0">
                    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
                COUNT(1) 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 = 6
                AND recommendStream.TARGET_CHILD_STATUS = 3
                <if test="type != null and type > 0">
                    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(SUM(IFNULL(recommend.REWARD_ALL, 0)),0) 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
                    1 = 1
                    AND recommendStream.TARGET_MAIN_STATUS = 5
                    AND recommendStream.TARGET_CHILD_STATUS = 1
                    AND interview.INTERVIEW_ROUND = 1
                    AND recommendStream.SOURCE_MAIN_STATUS != 5
                    AND recommendStream.SOURCE_CHILD_STATUS != 1
                    <if test="type != null and type > 0">
                        AND company.TYPE = #{type}
                    </if>
                    <include refid="first_interview_time" />
                GROUP BY
                    recommendStream.HUNTER_ADMIN_ID
                UNION ALL
                -- 非到面产品
                SELECT
                    IFNULL(SUM(IFNULL(recommend.REWARD_ALL, 0)),0) 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
                    1 = 1
                    AND recommendStream.TARGET_MAIN_STATUS = 6
                    AND recommendStream.TARGET_CHILD_STATUS = 1
                    <if test="type != null and type > 0">
                        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
                COUNT(1) 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 = 5
                AND recommendStream.SOURCE_CHILD_STATUS = 1
                AND recommendStream.TARGET_MAIN_STATUS = 5
                AND recommendStream.TARGET_CHILD_STATUS IN(5,2))
              OR
                (recommendStream.SOURCE_MAIN_STATUS = 5
                AND recommendStream.SOURCE_CHILD_STATUS = 1
                AND recommendStream.TARGET_MAIN_STATUS = 6)
                <if test="type != null and type > 0">
                    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
                COUNT(1) 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 = 6
                AND recommendStream.TARGET_CHILD_STATUS = 1
                <if test="type != null and type > 0">
                    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
                COUNT(1) 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 = 0
            AND customerStream.TARGET_SIGNED = 1
            <if test="type != null and type > 0">
                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(1,staff.DUTIES)
        <if test="roles != null and roles.size() >0">
            AND staff.ROLE_ID IN
            <foreach collection="roles" item="role" open="(" close=")" separator=",">
                #{role}
            </foreach>
        </if>

        <if test="salesAdminIds != null and salesAdminIds.size() > 0">
            AND staff.`ID` IN
            <foreach collection="salesAdminIds" item="userId" open="(" close=")" separator=",">
                #{userId}
            </foreach>
        </if>
        <include refid="commond_order_by"/>
        LIMIT 0, #{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
                COUNT(1) 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 = 1
                AND customerStream.TARGET_SIGNED = 1
                <include refid="signed_time" />
            GROUP BY
                customerStream.SALES_ADMIN
            ) signed ON signed.SALES_ADMIN = staff.ID
         WHERE
        FIND_IN_SET(3,staff.DUTIES)
        AND staff.ROLE_ID IN (1, 2, 5, 6, 7, 8)
        <if test="salesAdminIds != null and salesAdminIds.size() > 0">
            AND staff.`ID` IN
            <foreach collection="salesAdminIds" item="userId" open="(" close=")" separator=",">
                #{userId}
            </foreach>
        </if>
        ORDER BY signed.signTotal,signed.signTime
        LIMIT 0, #{maxRows};
    </select>


    <!-- 指定组排行榜 -->
    <select id="selectByOrganization" resultType="com.lieni.tuimu.dim.model.ExtraRankingList">
        SELECT
            organization.`ID` AS 'groupId',
            organization.`NAME` AS 'groupName',
            IFNULL(reward.rewardTotal, 0) AS 'rewardTotal',
            IFNULL(pament.pamentTotal,0) AS 'pamentTotal',
            IFNULL(signed.signTotal, 0) AS 'signTotal',
            IFNULL(entery.enteryTotal, 0) AS 'enteryTotal',
            IFNULL(placement.offerCountTotal, 0) AS 'offerCountTotal',
            IFNULL(interviewed.interviewedTotal, 0) AS 'interviewedTotal'
        FROM
          `T_DIM_TUIMU_ORGANIZATION` organization
        LEFT JOIN (
           <!--  按组的已回款总数 -->
            SELECT
                IFNULL(SUM(IFNULL(payment.RECEIVED_PAYMENTS_AMOUNT,0)),0) AS 'rewardTotal',
                <if test="organizations != null and organizations.size() > 0">
                    CASE
                    <foreach collection="organizations" item="organization">
                        WHEN SUBSTRING_INDEX(payment.HUNTER_ADMIN_ORGANIZATION, ',' ,1) IN
                        <foreach collection="organization.children" item="child" open="(" close=")" separator=",">
                            #{child}
                        </foreach>
                        THEN #{organization.parentId}
                    </foreach>
                    ELSE 0
                    END AS 'organizationId'
                </if>
                <if test="organizations == null or organizations.size() &lt;= 0">
                    SUBSTRING_INDEX(payment.HUNTER_ADMIN_ORGANIZATION, ',' ,1) AS 'organizationId'
                </if>
            FROM
              T_DIM_TUIMU_PAYMENT payment
            LEFT JOIN T_DIM_TUIMU_COMPANY company ON payment.COMPANY_ID = company.ID
            WHERE
                1 = 1
                <if test="type != null and type > 0">
                    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
                SUM(IFNULL(pament.rewardAll,0)) AS pamentTotal,
                pament.organizationId
            FROM
                -- 到面产品
                (
                SELECT
                    recommend.REWARD_ALL AS rewardAll,
                    -- offer加推荐的总佣金
                    <if test="organizations != null and organizations.size() > 0">
                        CASE
                        <foreach collection="organizations" item="organization">
                            WHEN SUBSTRING_INDEX(recommendStream.HUNTER_ADMIN_ORGANIZATION, ',' ,1) IN
                            <foreach collection="organization.children" item="child" open="(" close=")" separator=",">
                                #{child}
                            </foreach>
                            THEN #{organization.parentId}
                        </foreach>
                        ELSE 0
                        END AS 'organizationId'
                    </if>
                    <if test="organizations == null or organizations.size() &lt;= 0">
                        SUBSTRING_INDEX(recommendStream.HUNTER_ADMIN_ORGANIZATION, ',' ,1) 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
                    1 = 1
                    AND recommendStream.TARGET_MAIN_STATUS = 5
                    AND recommendStream.TARGET_CHILD_STATUS = 1
                    AND interview.INTERVIEW_ROUND = 1
                    AND recommendStream.SOURCE_MAIN_STATUS != 5
                    AND recommendStream.SOURCE_CHILD_STATUS != 1
                    <if test="type != null and type > 0">
                        AND company.TYPE = #{type}
                    </if>
                    <include refid="first_interview_time" />
                UNION ALL
                -- 非到面产品
                SELECT
                    recommend.REWARD_ALL AS rewardAll,
                    <if test="organizations != null and organizations.size() > 0">
                        CASE
                        <foreach collection="organizations" item="organization">
                            WHEN SUBSTRING_INDEX(recommendStream.HUNTER_ADMIN_ORGANIZATION, ',' ,1) IN
                            <foreach collection="organization.children" item="child" open="(" close=")" separator=",">
                                #{child}
                            </foreach>
                            THEN #{organization.parentId}
                        </foreach>
                        ELSE 0
                        END AS 'organizationId'
                    </if>
                    <if test="organizations == null or organizations.size() &lt;= 0">
                        SUBSTRING_INDEX(recommendStream.HUNTER_ADMIN_ORGANIZATION, ',' ,1) 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
                    1 = 1
                    AND recommendStream.TARGET_MAIN_STATUS = 6
                    AND recommendStream.TARGET_CHILD_STATUS = 1
                    <if test="type != null and type > 0">
                        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,
                    <if test="organizations != null and organizations.size() > 0">
                        CASE
                        <foreach collection="organizations" item="organization">
                            WHEN SUBSTRING_INDEX(customerStream.SALES_ADMIN_ORGANIZATION, ',' ,1) IN
                            <foreach collection="organization.children" item="child" open="(" close=")" separator=",">
                                #{child}
                            </foreach>
                            THEN #{organization.parentId}
                        </foreach>
                        ELSE 0
                        END AS 'organizationId'
                    </if>
                    <if test="organizations == null or organizations.size() &lt;= 0">
                        SUBSTRING_INDEX(customerStream.SALES_ADMIN_ORGANIZATION, ',' ,1) 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 = 0
                    AND customerStream.TARGET_SIGNED = 1
                    <if test="type != null and type > 0">
                        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,
                    <if test="organizations != null and organizations.size() > 0">
                        CASE
                        <foreach collection="organizations" item="organization">
                            WHEN SUBSTRING_INDEX(recommendStream.HUNTER_ADMIN_ORGANIZATION, ',' ,1) IN
                            <foreach collection="organization.children" item="child" open="(" close=")" separator=",">
                                #{child}
                            </foreach>
                            THEN #{organization.parentId}
                        </foreach>
                        ELSE 0
                        END AS 'organizationId'
                    </if>
                    <if test="organizations == null or organizations.size() &lt;= 0">
                        SUBSTRING_INDEX(recommendStream.HUNTER_ADMIN_ORGANIZATION, ',' ,1) 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
                AND company.TYPE =0
                WHERE
                    recommendStream.SOURCE_MAIN_STATUS = 6
                    AND recommendStream.SOURCE_CHILD_STATUS = 1
                    AND recommendStream.TARGET_MAIN_STATUS = 6
                    AND recommendStream.TARGET_CHILD_STATUS = 3
                    <if test="type != null and type > 0">
                        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,
                    <if test="organizations != null and organizations.size() > 0">
                        CASE
                        <foreach collection="organizations" item="organization">
                            WHEN SUBSTRING_INDEX(recommendStream.HUNTER_ADMIN_ORGANIZATION, ',' ,1) IN
                            <foreach collection="organization.children" item="child" open="(" close=")" separator=",">
                                #{child}
                            </foreach>
                            THEN #{organization.parentId}
                        </foreach>
                        ELSE 0
                        END AS 'organizationId'
                    </if>
                    <if test="organizations == null or organizations.size() &lt;= 0">
                        SUBSTRING_INDEX(recommendStream.HUNTER_ADMIN_ORGANIZATION, ',' ,1) 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 = 6
                    AND TARGET_CHILD_STATUS = 1
                    <if test="type != null and type > 0">
                        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,
                    <if test="organizations != null and organizations.size() > 0">
                        CASE
                        <foreach collection="organizations" item="organization">
                            WHEN SUBSTRING_INDEX(recommendStream.HUNTER_ADMIN_ORGANIZATION, ',' ,1) IN
                            <foreach collection="organization.children" item="child" open="(" close=")" separator=",">
                                #{child}
                            </foreach>
                            THEN #{organization.parentId}
                        </foreach>
                        ELSE 0
                        END AS 'organizationId'
                    </if>
                    <if test="organizations == null or organizations.size() &lt;= 0">
                        SUBSTRING_INDEX(recommendStream.HUNTER_ADMIN_ORGANIZATION, ',' ,1) 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 = 5
                            AND recommendStream.SOURCE_CHILD_STATUS = 1
                            AND recommendStream.TARGET_MAIN_STATUS = 5
                            AND recommendStream.TARGET_CHILD_STATUS IN(5,2))
                        OR
                            (recommendStream.SOURCE_MAIN_STATUS = 5
                            AND recommendStream.SOURCE_CHILD_STATUS = 1
                            AND recommendStream.TARGET_MAIN_STATUS = 6)
                    <if test="type != null and type > 0">
                        AND company.TYPE = #{type}
                    </if>
                    <include refid="interview_time" />
                ) interview_temp
            GROUP BY
                interview_temp.organizationId
                ) interviewed ON organization.ID = interviewed.organizationId
            <where>
                1 = 1
                <if test="organizations != null and organizations.size() > 0">
                    AND organization.`ID` IN
                    <foreach collection="organizations" item="organization" open="(" close=")" separator=",">
                        #{organization.parentId}
                    </foreach>
                </if>
            </where>
            <include refid="commond_order_by"/>
            LIMIT 0, #{maxRows};
    </select>

</mapper>

 

转载于:https://www.cnblogs.com/LTmei/p/8932338.html

任务4.8.4 利用Spark SQL实现分组排行榜
howard2005的专栏
06-22 1104
本实战任务展示了如何使用Spark SQL对数据进行分组和TopN计算，这是大数据领域中常见的数据处理需求。通过Spark SQL的窗口函数，可以方便地实现复杂的数据分析任务。
一些复杂的SQL及常用SQL
hanjiangxue的专栏
09-01 8837
一些常用的SQL--获得表的列数select count(1) from syscolumns where id=object_id(tableName)--修改表名,把表a改为bexec sp_rename a,b--关闭自动增长列set identity_insert Jxt_DeviceManage_Config on--关闭外键约束ALTER TABLE tableName N
sql复杂查询例子大全
05-16
sql复杂查询例子大全sql复杂查询例子大全
sql 复杂查询，一些经典案例
07-12
sql 复杂查询，一些经典查询，希望对朋友有帮助！一些经典案例
SQLServer数据库复杂查询示例
07-22
SQLServer数据库复杂查询示例，带数据库脚本。希望能帮助学习SQL语言的读者。
SQL 的复杂查询语句，例子及解析
默默不代表沉默
03-09 7643
select *from emp where deptno= (select deptno from emp where ename="smith") 这是查询出与smith同一部门的员工信息。 ----------------------------------------------------------------------------------------------------- ...
sql复杂实例
basycai的博客
08-06 9457
from：http://www.wzsky.net/html/Program/DataBase/35200.html 1.查询语句的使用 　　 　　使用 select语句和子查询(subquery)可以从一个或多个表,视图,实体试图中返回数据. 　　 　　1.1相关子查询 　　 　　可以将子查询(as subquery)或in或exists当成where的一个条件的一部分,这样的查询
MyBatis 动态 SQL 示例
04-28
附件是MyBatis 动态 SQL 示例，MyBatis 是一个持久层框架，它允许用户在 XML 文件中编写动态 SQL 语句。MyBatis 的动态 SQL 功能非常强大，它允许开发者根据运行时的条件动态地生成 SQL 语句。这使得 MyBatis 能够...
MyBatis 动态SQL示例
04-28
附件是MyBatis 动态SQL示例，MyBatis 是一个持久层框架，它允许用户在 XML 文件中编写动态 SQL 语句。MyBatis 的动态 SQL 功能非常强大，它允许开发者根据运行时的条件动态地生成 SQL 语句。这使得 MyBatis 能够灵活...
MySQL常用SQL语句总结包含复杂SQL查询
09-09
MySQL是世界上最流行的开源关系型数据库管理系统之一，其SQL语句是进行数据操作的基础。本篇文章主要总结了MySQL...在实际应用中，还可以结合其他高级查询技术，如JOIN、子查询、聚合函数等，来实现更复杂的业务需求。
单条SQL语句实现复杂逻辑几例
10-15
单条SQL语句实现复杂逻辑几例。本文简单明了，介绍了用单条SQL语句，就能实现复杂逻辑的例子。比较有用。
SQL复杂查询例子
北枫林
04-29 1575
SQL语句中复杂查询分为 —— 聚合查询/分组查询 聚合查询 示例：查询出所有员工的数量 SELECT COUNT(*)FROM emp; #推荐写法 SELECT COUNT(ename)FROM emp; SELECT COUNT(eid)FROM emp; SELECT COUNT(deptId)FROM emp; ...
一个复杂的数据需求的MySQL方案
congbao6525的博客
06-22 179
前些天处理了一个需求，当时的数据库环境是Oracle，我算是想尽了Oracle相关的方案，而且在问题的处理过程中，还在不断的琢磨，如果失败了还有什么其他的方案。 所以尽管Oracle这么一个成熟的商...
SQL复杂查询功能介绍及示例
最新发布
2301_77163982的博客
12-07 2643
复杂查询通常涉及多表连接、子查询、聚合函数、条件筛选、分组、排序等元素，并可能包含窗口函数、递归查询等高级特性。它们能够对数据进行深层次的加工和转换，揭示隐藏在数据背后的有价值信息
sql复杂案例
weixin_30421525的博客
05-22 257
　　工作中往往会遇到非常棘手的数据查询,运营人员不知道你的数据库表是如何设计的,也不知道你的数据库记录了啥数据,他只知道自己需要看什么数据,甚至有些数据根本就不存在. 　　单表查询的难度:一张数据库的表table,如下图,一个房间两个人对局,房主houser 挑战者challenger,只记录了赢家winner,以及分数point 　　需求,列出 玩家,今天输...
复杂SQL代码实例
weixin_34087503的博客
03-14 205
DECLARE @begin DATETIME,@end DATETIME,@shanghutype INT, @beginshanghuarea BIGINT ,@endshanghuarea bigint SELECT @begin='1753-01-01',@end='9999-12-31',@shanghutype=1,@beginshanghuarea=-1,@endshanghu...
mysql复杂sql书写示例
qq_32389095的博客
08-15 588
由于在导入参数${days}时，会自动使用双引号包起来，所以即使传参为now()，也会变成"now()"，date(“now()”)=null，所以会调用后面的默认方法获取数据。这种方式更适合显示成表格。
SQL_递归查询(复杂查询示例)
weixin_30546189的博客
08-02 133
需求: 一篇文章里有很多评论,每个评论又有很多回复评论,要求: 页面将文章展示出来,且文章的主评论按照评论时间分页展示,回复评论的评论完全展示在每个主评论下面,且按照回复时间排序 最终查询结果SQL查询结果如下: Code: 评论编码,ParentCode:回复评论编码,num:主评论序号,lvl:评论层级,CreateTime:评论创建时间 SQL详细过程如下: 1-先创建我...
SQL Server示例数据库备份文件快速导入指南
同时，示例数据库作为学习和开发过程中的辅助工具，可以大大加快学习进程并帮助理解复杂数据模型。在实际工作中，无论是利用管理工具SSMS、编写T-SQL语句，还是使用PowerShell脚本，都将提升工作效率和操作的准确性...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值