征信修复

select<br>&nbsp; &nbsp; a.guarantee_id as guarantee_id,&nbsp; &nbsp; &nbsp;--- 标的<br>&nbsp; &nbsp; min(b.loan_uid) as uid,&nbsp; &nbsp; --- uid<br>&nbsp; &nbsp; max(a.repay_balance * b.proportion/100) as repay_balance,&nbsp; &nbsp;--- 催收余额(分)<br>&nbsp; &nbsp; min(date(a.report_date)) as rpt_date,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;--- 首次报送日期(全额代偿时间)<br>&nbsp; &nbsp; sum(a.rpt_date_code='20') as has_close&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; --- 该笔标的是否结清<br>from collection_msg a<br>inner join collection b<br>on a.guarantee_id=b.guarantee_id<br>group by 1<br>order by 3 desc

1. rpt_date&gt;=2020-01-01 and rpt_date&lt;2026-01-01

2. repay_balance &lt;= 10000*100

3. has_close=0

select<br>&nbsp; &nbsp; a.guarantee_id as guarantee_id,&nbsp; &nbsp; &nbsp;--- 标的<br>&nbsp; &nbsp; min(b.loan_uid) as uid,&nbsp; &nbsp; --- uid<br>&nbsp; &nbsp; max(a.repay_balance * b.proportion/100) as repay_balance,&nbsp; &nbsp;--- 催收余额(分)<br>&nbsp; &nbsp; min(date(a.report_date)) as rpt_date,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;--- 首次报送日期(全额代偿时间)<br>&nbsp; &nbsp; sum(a.rpt_date_code='20') as has_close&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; --- 该笔标的是否结清<br>from collection_msg a<br>inner join collection b<br>on a.guarantee_id=b.guarantee_id<br>group by 1<br>order by 3 desc

1. rpt_date&gt;=2020-01-01 and rpt_date&lt;2026-01-01

2. 剔除has_close=1的uid数据或者repay_balance &gt; 10000*100的uid数据