- select *
- from (select T.*,
- ROW_NUMBER() OVER(PARTITION BY t.machine_no order by t.rent_code, t.period asc) rn
- from (select distinct rcon.rent_code,
- agen.agen_name,
- cust.cust_name,
- re.prod_sort,
- re.machine_no,
- re.prod_model,
- re.price,
- payscheme.start_date,
- (payscheme.lease_num * payscheme.pay_cycle) qixian,
- payplan.period,
- payscheme.first_date,
- payplan.pay_date,
- round(payplan.rent, 2),
- round(payplan.principal, 2),
- round(payplan.interest, 2)
- from fl_fund_overdue overdue
- left join fl_rent_payscheme payscheme
- on payscheme.reps_id = overdue.reps_id
- left join fl_rent_equipment re
- on re.reeq_id = payscheme.reeq_id
- left join fl_rent_contract rcon
- on rcon.rent_id = re.rent_id
- left join fl_agen_agent agen
- on agen.agen_id = rcon.agen_id
- left join fl_cust_custom cust
- on cust.cust_id = rcon.cust_id
- left join (select *
- from fl_rent_payplan pay
- where pay.delete_flag = 0
- and pay.paid_status = 0) payplan
- on payplan.period = overdue.payable_period
- where overdue.delete_flag = 0
- and payscheme.delete_flag = 0
- and re.delete_flag = 0
- and rcon.delete_flag = 0
- and agen.delete_flag = 0
- and cust.delete_flag = 0
- and payscheme.status = 1
- and payplan.delete_flag = 0
- and payplan.paid_status = 0
- order by rcon.rent_code, payplan.period asc) T)
- where rn = 1
复制代码 |