【Mysql】纯sql报表

【博客地址】：https://blog.csdn.net/sunyaowu315
【博客大纲地址】：https://blog.csdn.net/sunyaowu315/article/details/82905347

---

sql开发报表，制作视图，是所有出表方式中最直接高效的方法之一，
考察一个小伙伴的sql能力，可以试着问下他能否利用纯sql制作线上报表。

报表1：金融业务贷后-逾期表（即期）

代码展示

SELECT 
DATE_FORMAT(a.endtime, '%Y-%m-%d') as '日期',
TRUNCATE(sum(b.money),0) '正常应还$',
TRUNCATE(sum(if(a.`status` != 2 or a.success_status = 3,b.money,0)),0)'首次逾期$',
concat(TRUNCATE(sum(if(a.`status` != 2 or a.success_status = 3,b.money,0))/sum(b.money)*100, 2),'%') '首次逾期率$%',
TRUNCATE(sum(if(a.`status` != 2,b.money,0)),0)'当前逾期$',
concat(TRUNCATE(sum(if(a.`status` != 2,b.money,0))/sum(b.money)*100, 2),'%') '当前逾期率$%',
count(a.apply_id) '正常应还#',
sum(if(a.`status` != 2 or a.success_status = 3,1,0)) '首次逾期#',
concat(TRUNCATE(sum(if(a.`status` != 2 or a.success_status = 3,1,0))/count(a.apply_id)*100, 2),'%') '首次逾期率#%',
sum(if(a.`status` != 2,1,0)) '当前逾期#',
concat(TRUNCATE(sum(if(a.`status` != 2,1,0))/count(a.apply_id)*100, 2),'%') '当前逾期率#%',
concat(TRUNCATE(sum(if(l.count>1,b.money,0))/sum(b.money)*100, 2),'%') '续贷占比$% '
from app_repayment a
left join app_A b on b.apply_id = a.apply_id
left join app_B c on c.id = a.apply_id
left join app_C d on d.id = c.user_id
left join app_D e on e.id = d.channel_id
left join (SELECT a.id user_id,count(b.id) count from app_A a left join app_B b on b.user_id =a.id group by a.id) l on l.user_id = d.id
where a.endtime BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) and DATE_SUB(CURDATE(), INTERVAL -1 DAY)
and e.channel = '渠道A'
GROUP BY DATE_FORMAT(a.endtime, '%Y-%m-%d') 
desc
limit 7

结果展示

报表2：金融业务贷后-逾期表（递延）

代码展示

稍等片刻

结果展示