27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT
t.`userName` 申请人姓名,
t.`userPhone` 申请人手机号,
d.city 公寓所在城市,
CASE
WHEN d.topCompanyId IS NULL OR d.topCompanyId=1 THEN d.briefName
ELSE d.topCompanyName
END AS 申请公寓,
t.`loanMoney`/100 借款金额,
t.loanLease 借款总期数,
a.createTime 放款时间,
CASE
WHEN t.`rentStartDate` IS NOT NULL THEN t.`rentStartDate`
ELSE e.payDate
END AS 首次还款时间,
CASE
WHEN t.orderStatus=4100 THEN t.`loanMoney`/100-e.payAmount
WHEN t.orderStatus=6000 THEN SUBSTRING_INDEX(f.checkRecord,',',1)
ELSE 0
END AS 贷款余额,
CASE
WHEN t.orderStatus=4100 THEN '还款中'
WHEN t.orderStatus=5100 THEN '已还清'
WHEN t.orderStatus=6000 THEN '清退未结清'
ELSE '清退已结清'
END AS 订单状态
FROM
t_yuefu_v4_agentorder t
LEFT JOIN (
SELECT b.orderId,MIN(b.payDate) AS payDate,
SUM(CASE WHEN b.recordStatus >= 0 AND b.payStatus= 1 THEN b.payAmount / 100 ELSE 0 END) AS payAmount
FROM t_yuefu_v4_billcheck b
GROUP BY b.orderId) e ON e.orderId=t.id
LEFT JOIN t_yuefu_v4_orderLog a ON a.orderId=t.id AND a.checkStep=4100 AND a.recordStatus>= 0
LEFT JOIN t_yuefu_v4_orderLog f ON f.orderId=t.id AND f.checkStep=6000 AND f.recordStatus>= 0
LEFT JOIN t_yuefu_v4_orderLog b ON t.id = b.orderId AND b.checkStep=6100 AND b.recordStatus>= 0
JOIN t_yuefu_v4_agentcompany d ON t.companyId = d.id AND d.recordStatus>=0
LEFT JOIN (
SELECT b.orderId, b.realPayDate
FROM t_yuefu_v4_billcheck b WHERE b.period=b.totalPeriods
) g ON g.orderId=t.id
WHERE
t.recordStatus >= 0
AND (t.orderStatus IN (4100,6000) OR
(t.orderStatus=5100 AND g.realPayDate>='20160801') OR
(t.orderStatus=6100 AND b.createTime>='20160801'))
ORDER BY t.createTime;
INNER JOIN t_yuefu_v4_orderLog b ON t.id = b.orderId AND b.checkStep=6100 AND b.recordStatus>= 0
JOIN t_yuefu_v4_agentcompany d ON t.companyId = d.id AND d.recordStatus>=0
INNER JOIN (
SELECT b.orderId, b.realPayDate
FROM t_yuefu_v4_billcheck b WHERE b.period=b.totalPeriods
) g ON g.orderId=t.id
2. 表及字段 t_yuefu_v4_agentorder(orderStatus,createTime) 可以增加一个索引。但where 中的 or 有可能导致使用不了索引,改为 union 上下合并结果集。 表t_yuefu_v4_billcheck(orderId) 可以增加索引。