记录一下,写的最长的sql

版权声明:本文为qingfeng2556所创,欢迎转载,转载请附加来源。 https://blog.csdn.net/wuhenzhangxing/article/details/79959407
 SELECT  * from (
        SELECT
        tp.id,
        tp.status,
        tp.number,
        tu.user_id,
        tu.login_name,
        tub.real_name,
        tu.phone,
        tp.resale_price_ask,
        tp.first_price_loan,
        tp.unit,
        tp.deadline,
        DATE_FORMAT(tp.loan_time,"%Y-%m-%d %T") as loan_time,
        tr.repayment_date,
       (CASE tp.`status`
            WHEN '8' THEN
                DATE_FORMAT(GREATEST(tr.reality_date,tr.commit_time),"%Y-%m-%d %T")
            ELSE
                NULL
            END ) AS "reality_date",
      tp.funds_name,
      (
            CASE tp.`status`
            WHEN '7' THEN
                (CASE
                WHEN tr.way IN (2, 3) THEN
                    datediff(now(), tr.repayment_date)
                ELSE
                0
                END)
            WHEN '8' THEN
                (case tr.way
                  when '4' then
                      datediff(GREATEST(tr.reality_date,tr.commit_time), tr.repayment_date)
                  ELSE 0 END
                )
            ELSE
                0
            END
        ) AS overdueDay,
        (sum(tr.principal) + sum(tr.interest)) as currentBalance,
         sum(tr.overdue) as currentOverdue,
        (sum(tr.principal) + sum(tr.interest) - sum(tr.reality_principal) - sum(tr.reality_interest)+(
            case tr.way
            when '2' then sum(tr.overdue)
              when '3' then sum(tr.overdue)
              else "0.00"
            end
        )-sum(tr.reality_overdue)
        ) as currentTotal,
        tr.way,
        (sum(tr.reality_principal) + sum(tr.reality_interest) + sum(tr.reality_overdue)+ sum(tr.reality_default)) as alreadyBalance,
        tclo.flow_no
        FROM
            t_project tp
        LEFT JOIN t_user tu ON tp.user_id = tu.user_id
        LEFT JOIN t_user_basis tub ON tub.user_id = tu.user_id
        LEFT JOIN t_project_refund tr ON tp.id = tr.project_id
        LEFT JOIN t_cash_loan_order tclo on tp.id = tclo.project_id
        where 1=1
        and  tp.`status` in ('6','7','8','9')
        <if test="number != null and number != '' " >
            and tp.number like '%${number}%'
        </if>
        <if test="userName != null and userName != '' " >
            and tu.login_name like '%${userName}%'
        </if>
        <if test="realName != null and realName != '' " >
            and tub.real_name like '%${realName}%'
        </if>
        <if test="phone != null and phone != '' " >
            and tu.phone like '%${phone}%'
        </if>
        <if test="startLoanTime != null and startLoanTime != '' " >
            <![CDATA[and DATE_FORMAT(tp.loan_time,'%Y%m%d') >= DATE_FORMAT(#{startLoanTime,jdbcType=VARCHAR},'%Y%m%d')]]>
        </if>
        <if test="endLoanTime != null and endLoanTime != '' " >
            <![CDATA[and DATE_FORMAT(tp.loan_time,'%Y%m%d') <= DATE_FORMAT(#{endLoanTime,jdbcType=VARCHAR},'%Y%m%d')]]>
        </if>
        <if test="startRepaymentTime != null and startRepaymentTime !=''" >
            <![CDATA[and DATE_FORMAT(tr.repayment_date,'%Y%m%d') >= DATE_FORMAT(#{startRepaymentTime,jdbcType=VARCHAR},'%Y%m%d')]]>
        </if>
        <if test="endRepaymentTime != null and endRepaymentTime !=''" >
            <![CDATA[and DATE_FORMAT(tr.repayment_date,'%Y%m%d') <= DATE_FORMAT(#{endRepaymentTime,jdbcType=VARCHAR},'%Y%m%d')]]>
        </if>
        <if test='timeLimit !=null and timeLimit=="0" ' >
            and tp.unit = '0' and tp.deadline=15
        </if>
        <if test='timeLimit !=null  and timeLimit=="1" '>
            and tp.unit='1' and tp.deadline=1
        </if>


        <if test="applyAmount != null and applyAmount != '' " >
            and tp.resale_price_ask=#{applyAmount,jdbcType=VARCHAR}
        </if>
        <if test="fundChannel != null and fundChannel != '' " >
            and tp.funds_id=#{fundChannel,jdbcType=VARCHAR}
        </if>


        <if test='repayStatus !=null  and repayStatus=="2" '>
            and tr.`status`='1'
            and tr.way in('2','3')
            and tp. status = "6"
        </if>


        <if test='repayStatus !=null  and repayStatus=="3" '>
            and tp.status="8"
        </if>
        <if test='repayStatus !=null  and repayStatus=="4" '>
            and DATE_FORMAT(tr.repayment_date,'%Y%m%d')= DATE_FORMAT(tr.reality_date,'%Y%m%d')
        </if>
        <if test='repayStatus !=null  and repayStatus=="5" '>
            and tp.status="8" and tr.status="1"
        </if>
        <if test='repayStatus !=null  and repayStatus=="6" '>
            and tp.status="8" and tr.status='0'
        </if>
        <if test='repayStatus !=null  and repayStatus=="7" '>
            and tr.way="3"
        </if>
        <if test='repayStatus !=null  and repayStatus=="8" '>
            and tp.status="7"
        </if>
        <if test='userType !=null  and userType=="1" '>
            and EXISTS(
            SELECT t2.user_id
            from t_project t2
            where t2.status='8'
            and t2.user_id=tu.user_id
            )
        </if>
        <if test='userType !=null  and userType=="2" '>
            and not EXISTS(
            SELECT t2.user_id
            from t_project t2
            where t2.status='8'
            and t2.user_id=tu.user_id
            )
        </if>
group by tp.id)ttt
where 1=1
<if test='startOverdueDay !=null'>
<![CDATA[and overdueDay>= #{startOverdueDay,jdbcType=INTEGER}]]>
</if>
<if test='endOverdueDay !=null'>
<![CDATA[and overdueDay<= #{endOverdueDay,jdbcType=INTEGER}]]>
</if>
阅读更多 登录后自动展开
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页