多条件、多维度 实现查询汇总

复杂的业务逻辑，用sql实现


    <select id="queryDailyDatabySupplierType" parameterType="com.jd.jrdp.mybatis.page.PageBean" resultType="java.util.HashMap">
        select gsqci.inter_name,
        gsqci.inter_sign,
        gabil.business_name,
        gabil.department_name,
        gasl.supplier_id,
        gasl.supplier_name,
        SUM(daily_total_count) as daily_total_count,
        SUM(actual_request_count) as actual_request_count,
        SUM(have_result) as have_result,SUM(no_result) as no_result,
        SUM(have_result) + SUM(no_result) as success_count,
        SUM(have_result) /(SUM(have_result) + SUM(no_result)) as check_rate,
        <choose>
            <when test='params!=null and params.businessType !=null and params.businessType =="1"'>
                gsqci.business_id,
            </when>
            <otherwise>
                'TOTAL' as business_id,
            </otherwise>
        </choose>
        <choose>
            <when test='params!=null and params.dateFormat !=null and params.dateFormat =="1"'>
                date_format(gsqci.daily, '%Y') as years
            </when>
            <when test='params!=null and params.dateFormat !=null and params.dateFormat =="2"'>
                date_format(gsqci.daily, '%Y-%m') as months
            </when>
            <when test='params!=null and params.dateFormat !=null and params.dateFormat =="0"'>
                'TOTAL' as daily
            </when>
            <otherwise>
                daily
            </otherwise>
        </choose>
        from graviation_sts_qv_count_info gsqci
        inner join graviation_sts_batch_sql_list gsbsl on gsqci.inter_sign = gsbsl.inter_sign
        inner join graviation_api_supplier_list gasl on gsbsl.supplier_id = gasl.supplier_id
        inner JOIN graviation_api_business_id_list gabil on gsqci.business_id = gabil.business_id
        <where>
            <if test="params!=null and params.interSign !=null">
                and gsqci.inter_sign=#{params.interSign}
            </if>
            <if test="params!=null and params.businessName !=null">
                and #{params.businessName}= gabil.business_name
            </if>
            <if test="params!=null and params.departmentName !=null">
                and #{params.departmentName}= gabil.department_name
            </if>
            <if test="params!=null and params.supplierId !=null">
                and #{params.supplierId}= gsbsl.supplier_id
            </if>
            <choose>
                <when test='params!=null and params.dateFormat !=null and params.dateFormat =="1"'>
                    <if test="params!=null and params.beginDate !=null">
                        and date_format(gsqci.daily, '%Y')>= #{params.beginDate}
                    </if>
                    <if test="params!=null and params.endDate !=null">
                        and #{params.endDate}>= date_format(gsqci.daily, '%Y')
                    </if>
                </when>
                <when test='params!=null and params.dateFormat !=null and params.dateFormat =="2"'>
                    <if test="params!=null and params.beginDate !=null">
                        and date_format(gsqci.daily, '%Y-%m')>= #{params.beginDate}
                    </if>
                    <if test="params!=null and params.endDate !=null">
                        and #{params.endDate}>= date_format(gsqci.daily, '%Y-%m')
                    </if>
                </when>
                <otherwise>
                    <if test="params!=null and params.beginDate !=null">
                        and gsqci.daily>= #{params.beginDate}
                    </if>
                    <if test="params!=null and params.endDate !=null">
                        and #{params.endDate}>= gsqci.daily
                    </if>
                </otherwise>
            </choose>
            and gsqci.business_id is not null and gsqci.business_id !=''
            and gsqci.inter_sign is not null and gsqci.inter_sign !=''
        </where>
        GROUP BY
        <choose>
            <when test='params!=null and params.dateFormat !=null and params.dateFormat =="1"'>
                <if test='params!=null and params.supplierType !=null and params.supplierType =="1"'>
                    gsbsl.supplier_id,
                </if>
                <if test='params!=null and params.interSignType !=null and params.interSignType =="1"'>
                    gsqci.inter_sign,
                </if>
                <if test='params!=null and params.departmentType !=null and params.departmentType =="1"'>
                    gabil.department_name,
                </if>
                <if test='params!=null and params.businessType !=null and params.businessType =="1"'>
                    gsqci.business_id,
                </if>
                years
            </when>
            <when test='params!=null and params.dateFormat !=null and params.dateFormat =="2"'>
                <if test='params!=null and params.supplierType !=null and params.supplierType =="1"'>
                    gsbsl.supplier_id,
                </if>
                <if test='params!=null and params.interSignType !=null and params.interSignType =="1"'>
                    gsqci.inter_sign,
                </if>
                <if test='params!=null and params.departmentType !=null and params.departmentType =="1"'>
                    gabil.department_name,
                </if>
                <if test='params!=null and params.businessType !=null and params.businessType =="1"'>
                    gsqci.business_id,
                </if>
                months
            </when>
            <when test='params!=null and params.dateFormat !=null and params.dateFormat =="0"'>
                <if test='params!=null and params.supplierType !=null and params.supplierType =="1"'>
                    <choose>
                        <when test='params!=null and params.interSignType !=null and params.interSignType =="1"'>
                            gsbsl.supplier_id,
                        </when>
                        <otherwise>
                            <choose>
                                <when test='params!=null and params.departmentType !=null and params.departmentType =="1"'>
                                    gsbsl.supplier_id,
                                </when>
                                <otherwise>
                                    <choose>
                                        <when test='params!=null and params.businessType !=null and params.businessType =="1"'>
                                            gsbsl.supplier_id,
                                        </when>
                                        <otherwise>
                                            gsbsl.supplier_id
                                        </otherwise>
                                    </choose>
                                </otherwise>
                            </choose>
                        </otherwise>
                    </choose>
                </if>
                <if test='params!=null and params.interSignType !=null and params.interSignType =="1"'>
                    <choose>
                        <when test='params!=null and params.departmentType !=null and params.departmentType =="1"'>
                            gsqci.inter_sign,
                        </when>
                        <otherwise>
                            <choose>
                                <when test='params!=null and params.businessType !=null and params.businessType =="1"'>
                                    gsqci.inter_sign,
                                </when>
                                <otherwise>
                                    gsqci.inter_sign
                                </otherwise>
                            </choose>
                        </otherwise>
                    </choose>

                </if>
                <if test='params!=null and params.departmentType !=null and params.departmentType =="1"'>
                    <choose>
                        <when test='params!=null and params.businessType !=null and params.businessType =="1"'>
                            gabil.department_name,
                        </when>
                        <otherwise>
                            gabil.department_name
                        </otherwise>
                    </choose>
                </if>
                <if test='params!=null and params.businessType !=null and params.businessType =="1"'>
                    gsqci.business_id
                </if>
            </when>
            <otherwise>
                <if test='params!=null and params.supplierType !=null and params.supplierType =="1"'>
                    gsbsl.supplier_id,
                </if>
                <if test='params!=null and params.interSignType !=null and params.interSignType =="1"'>
                    gsqci.inter_sign,
                </if>
                <if test='params!=null and params.departmentType !=null and params.departmentType =="1"'>
                    gabil.department_name,
                </if>
                <if test='params!=null and params.businessType !=null and params.businessType =="1"'>
                    gsqci.business_id,
                </if>
                gsqci.daily
            </otherwise>
        </choose>
        ORDER BY
        <choose>
            <when test='params!=null and params.dateFormat !=null and params.dateFormat =="1"'>
                <if test='params!=null and params.supplierType !=null and params.supplierType =="1"'>
                    gsbsl.supplier_id,
                </if>
                <if test='params!=null and params.interSignType !=null and params.interSignType =="1"'>
                    gsqci.inter_sign,
                </if>
                <if test='params!=null and params.departmentType !=null and params.departmentType =="1"'>
                    gabil.department_name,
                </if>
                <if test='params!=null and params.businessType !=null and params.businessType =="1"'>
                    gsqci.business_id,
                </if>
                years
            </when>
            <when test='params!=null and params.dateFormat !=null and params.dateFormat =="2"'>
                <if test='params!=null and params.supplierType !=null and params.supplierType =="1"'>
                    gsbsl.supplier_id,
                </if>
                <if test='params!=null and params.interSignType !=null and params.interSignType =="1"'>
                    gsqci.inter_sign,
                </if>
                <if test='params!=null and params.departmentType !=null and params.departmentType =="1"'>
                    gabil.department_name,
                </if>
                <if test='params!=null and params.businessType !=null and params.businessType =="1"'>
                    gsqci.business_id,
                </if>
                months
            </when>
            <when test='params!=null and params.dateFormat !=null and params.dateFormat =="0"'>
                <if test='params!=null and params.supplierType !=null and params.supplierType =="1"'>
                    <choose>
                        <when test='params!=null and params.interSignType !=null and params.interSignType =="1"'>
                            gsbsl.supplier_id,
                        </when>
                        <otherwise>
                            <choose>
                                <when test='params!=null and params.departmentType !=null and params.departmentType =="1"'>
                                    gsbsl.supplier_id,
                                </when>
                                <otherwise>
                                    <choose>
                                        <when test='params!=null and params.businessType !=null and params.businessType =="1"'>
                                            gsbsl.supplier_id,
                                        </when>
                                        <otherwise>
                                            gsbsl.supplier_id
                                        </otherwise>
                                    </choose>
                                </otherwise>
                            </choose>
                        </otherwise>
                    </choose>
                </if>
                <if test='params!=null and params.interSignType !=null and params.interSignType =="1"'>
                    <choose>
                        <when test='params!=null and params.departmentType !=null and params.departmentType =="1"'>
                            gsqci.inter_sign,
                        </when>
                        <otherwise>
                            <choose>
                                <when test='params!=null and params.businessType !=null and params.businessType =="1"'>
                                    gsqci.inter_sign,
                                </when>
                                <otherwise>
                                    gsqci.inter_sign
                                </otherwise>
                            </choose>
                        </otherwise>
                    </choose>

                </if>
                <if test='params!=null and params.departmentType !=null and params.departmentType =="1"'>
                    <choose>
                        <when test='params!=null and params.businessType !=null and params.businessType =="1"'>
                            gabil.department_name,
                        </when>
                        <otherwise>
                            gabil.department_name
                        </otherwise>
                    </choose>
                </if>
                <if test='params!=null and params.businessType !=null and params.businessType =="1"'>
                    gsqci.business_id
                </if>
            </when>
            <otherwise>
                <if test='params!=null and params.supplierType !=null and params.supplierType =="1"'>
                    gsbsl.supplier_id,
                </if>
                <if test='params!=null and params.interSignType !=null and params.interSignType =="1"'>
                    gsqci.inter_sign,
                </if>
                <if test='params!=null and params.departmentType !=null and params.departmentType =="1"'>
                    gabil.department_name,
                </if>
                <if test='params!=null and params.businessType !=null and params.businessType =="1"'>
                    gsqci.business_id,
                </if>
                gsqci.daily
            </otherwise>
        </choose>
    </select>