1 select * 2 from ( 3 select f1.apply_no as applyNo, 4 f1.subject_code as subjectCode, 5 f1.subject_name as subjectName, 6 f1.contract_no as contractNo, 7 (case f1.cust_type 8 when 1 then '个人' 9 when 2 then '企业' end) as applyType, 10 CONCAT(LEFT(f1.cust_name, 1), '**') as custName, 11 (case f1.gender 12 when 'M' then '男' 13 when 'F' then '女' end) as gender, 14 (case f1.marriage 15 when 0 then '未婚' 16 when 1 then '已婚' end) as marriage, 17 f1.age as age, 18 f1.commpany_name as companyName, 19 CONCAT(LEFT(f1.telephone, 3), '****', RIGHT(f1.telephone, 4)) as telephone, 20 CONCAT(LEFT(f1.phone_num, 3), '****', RIGHT(f1.phone_num, 4)) as phoneNum, 21 f1.company_address as companyAddress, 22 (case f1.certificate_type 23 when 1 then '身份证' 24 when 2 then '军官证' 25 when 3 then '护照' 26 when 4 then '士兵证' 27 else '统一社会信用代码' end) as certificateType, 28 CONCAT(LEFT(f1.certificate_no, 6), '****', RIGHT(f1.certificate_no, 4)) as certificateNo, 29 (case f1.car_type 30 when '049500000' then '新车' 31 when '049500001' then '二手车' 32 else '' end) as carType, 33 (case f1.rental_type 34 when '0130000000000' then '正租' 35 when '0130000000001' then '回租' end) as rentalType, 36 f1.finance_num as financeNum, 37 f1.product_name as productName, 38 f1.car_manuft_mfr as manufacturer, 39 f1.car_brand as carBrand, 40 f1.car_model as carModel, 41 f1.car_style as carStyle, 42 f1.car_sale_price as carSalePrice, 43 CONCAT(LEFT(f1.vin, 3), '****', RIGHT(f1.vin, 3)) as vin, 44 f1.car_guidance_price as carGuidancePrice, 45 f1.total_investment as totalInvestment, 46 f1.finance_amount as financeAmount, 47 f1.cust_rate as custRate, 48 concat(f1.down_payment_ratio, '%') as downPaymentRatio, 49 f1.down_payment_amount as downPaymentAmount, 50 f1.tail_payment_ratio as tailPaymentRatio, 51 f1.tail_payment_amount as tailPaymentAmount, 52 f1.cust_service_rate as custServiceRate, 53 f1.cust_service_charge as custServiceCharge, 54 (case f1.service_charge_type 55 when '055900000' then '一次性' 56 when '055900001' 57 then '分期' end) as serviceChargeType, 58 f1.settle_service_charge as settleServiceCharge, 59 f1.discount_service as discountService, 60 f1.discount_amount as discountAmount, 61 f1.margin_radio as marginRadio, 62 f1.margin_amount as marginAmount, 63 (case f1.margin_type 64 when '0' then '不冲抵' 65 when '1' then '冲抵' end) as marginType, 66 f1.monthly_amount as monthlyAmount, 67 f1.apply_date as applyDate, 68 f1.contract_create_date as contractCreateDate, 69 f1.contract_effect_date as contractEffectDate, 70 f1.contract_end_date as contractEndDate, 71 f1.first_deduct_date as firstDeductDate, 72 f1.last_deduct_date as lastDeductDate, 73 f1.province_name as provinceName, 74 f1.city_name as cityName, 75 f1.car_loan_date as carLoanDate, 76 f1.deduct_num as deductNum, 77 f1.real_owner_car as realOwnerCar, 78 (case f1.contract_status 79 when '2' then '生效' 80 when '3' then '结清' 81 when '4' then '取消' 82 else '' end) as contractStatus, 83 (case f1.repayment_type 84 when '011200000' then '等额本息' 85 when '011200003' then '气球融' 86 when '011200004' then '先息后本' end) as repaymentType, 87 f1.insurance_amount as insuranceAmount, 88 f1.purchase_tax_amount as purchaseTaxAmount, 89 f1.gps_amount as gpsAmount, 90 f1.profession_name as professionName, 91 f1.annual_salary as annualSalary, 92 f1.cur_resi_city_name as curResiCityName, 93 f1.account_manager_amount as accountManagerAmount, 94 f2.asset_name as assetName, 95 f2.rec_account_no as recAccountNo, 96 (case f1.mark_type 97 when 1 then '已标记' 98 else '未标记' end) as markType, 99 f1.capital_name as capitalName, 100 f1.car_num as carNum, 101 f1.production_date as productionDate, 102 f3.sum_overdue_num as sumOverdueNum, 103 f3.sum_overdue_days as sumOverdueDays, 104 f3.max_overdue_days as maxOverdueDays, 105 f3.current_overdue_num as currentOverdueNum, 106 f3.remian_capital as remianCapital, 107 f3.remian_interest as remianInterest, 108 f3.remain_num as remainNum, 109 f3.remian_capital_interest as remianCapitalInterest, 110 sum(case when f4.finance_code in ('F060', 'F118') then f4.finance_amount else 0 end) as f060, 111 f4.finance_code 112 from apus.tinfo_asset_order_info f1 113 left join apus.tinfo_asset_base_info f2 on f1.asset_code = f2.asset_code 114 left join apus.tinfo_asset_afterloan_info f3 on f1.apply_no = f3.apply_no 115 left join apus.tinfo_order_financing_info f4 on f1.apply_no = f4.apply_no 116 where f1.apply_no = '1000289090' 117 ) t 118 group by t.finance_code;
行转列
Life is so short,do something to make yourself happy, such as coding.