mysql

select 
s.userId,
t.interestTime,
sum(
case 
when t.interestType = 'A1' 
then ((s.totalOnInvestedShare*t.yield/100 + s.addshare*s.addyield/100)*t.cycle/365)
else ((s.totalOnInvestedShare*t.yield/100 + s.addshare*s.addyield/100)*t.days/365)
end) as interest,
sum(case 
when t.interestType = 'A1'
then s.totalOnInvestedShare
else (if(t.interestTime=t.endDate,s.totalOnInvestedShare,0))
end
) as capital,
sum(
case 
when t.interestType = 'A1' 
then 0
else (if(t.planStatus='ADVANCE',(t.penaltyAmount/t.financedAmount*s.totalOnInvestedShare),0))
end
) as penalty
from 
zx_standard_statistics s 
left join
(select p.bidNo as bidNo,p.interestTime as interestTime,p.status as status,p.planStatus as planStatus,
i.yield as yield,
i.startDate as startDate,
i.endDate as endDate,
i.cycle as cycle,
i.financedAmount as financedAmount,
e.interestType as interestType,
a.penaltyAmount as penaltyAmount,
(
case 
when 
(select count(1) from zx_standard_plan pp where pp.interestTime < p.interestTime and pp.bidNo = p.bidNo order by pp.interestTime desc limit 1) > 0 
then (select TO_DAYS(date(from_unixtime(p.interestTime)))-TO_DAYS(date(from_unixtime((select pp.interestTime from zx_standard_plan pp where pp.interestTime < p.interestTime and pp.bidNo = p.bidNo order by pp.interestTime desc limit 1))))) 
else (select TO_DAYS(date(from_unixtime(p.interestTime)))-TO_DAYS(date(from_unixtime(i.startDate)))) 
end) as days
from
zx_standard_plan p
left join zx_standard_informations i
on p.bidNo = i.bidNo
left join zx_standard_extra e
on e.bidNo = p.bidNo
left join zx_advance_repay a
on a.bidNo = p.bidNo
where
p.planStatus <> 'VOID' and
i.bidType <> 'GREEN' and
i.bidStatus <> 'FINISHED'
order by p.interestTime asc) t on t.bidNo = s.bidNo where t.status = 'INIT' and t.interestTime >= unix_timestamp(now()) group by s.userId,t.interestTime;

sql语法并没有多复杂，复杂的是业务逻辑，记录的是思路

 

转载于:https://www.cnblogs.com/Smilence1024/p/7999330.html