记录一下,写的最长的sql

标签: mysql
9人阅读 评论(0) 收藏 举报
分类:
 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>
查看评论

花3个小时写的中国最长sql语句

  • 2010年07月09日 11:39
  • 5KB
  • 下载

迄今为止,我写过的最长的SQL语句

SELECT   wap_user.u_name, NVL (f_pv, 0), NVL (m_pv, 0), NVL (f_se, 0),          NVL (m_se, 0)     ...
  • jaketseng
  • jaketseng
  • 2006-12-29 00:00:00
  • 601

写的史上最长的sql语句

select c.nums as sums ,d.direction,e.intersection_name from  (select sum(t.numbers) as nums, b.segm...
  • kccacmilan
  • kccacmilan
  • 2013-01-05 09:09:33
  • 1435

SQL找某个字段最大的记录或连接

有一种经常遇到的情况可以小结一下,比如一个表中一个account_id对应多个loan_id,这个时候找最大的loan_id那条记录就是形如 SELECT DISTINCT ON(account...
  • strwolf
  • strwolf
  • 2016-03-02 11:35:12
  • 353

记录一下收获和感想

记录一下收获和感想
  • u011465055
  • u011465055
  • 2013-07-21 01:11:00
  • 200

开博客,记录一下自己的学习过程

大学毕业,开始工作了。发现自己大学过的太水,该学的什么都没学好,估计要在工作的时候还了。 姑且开个博记录一下学习过程。...
  • sunhao474
  • sunhao474
  • 2016-08-23 11:00:57
  • 89

hdu 1160 最长上升子序列 + 记录路径

FatMouse's Speed Time Limit: 2000/1000 MS (Java/Others)    Memory Limit: 65536/32768 K (Java/Others...
  • eletroram
  • eletroram
  • 2017-09-15 22:02:01
  • 110

写过最长的一次sql

SELECT (NVL(SJTOTAL, 0) - NVL(TOULIAN, 0) - NVL(WANNENG, 0)) * 0.1 +       (NVL(TOULIAN, 0)) + (NVL(...
  • wjx85840948
  • wjx85840948
  • 2010-08-09 15:44:00
  • 965

用SQL实现记录上下移动的思路

  在做管理系统时,不可避免会要求对记录进行上下移动.   假如我们有一张表 t_test ,它的字段如下:   CREATE TABLE [dbo].[t_test] (     [sysid] [...
  • sharetop
  • sharetop
  • 2008-03-04 10:05:00
  • 1532
    个人资料
    持之以恒
    等级:
    访问量: 31万+
    积分: 3884
    排名: 1万+