SQL查询

select distinct
t1.mcs_cre_credit_head_id,
t2.mcs_cre_credit_line_customer_change_head_id,
t1.bill_code,t2.customer_code,
t2.customer_name,
(CASE t2.has_married WHEN ‘wh‘ THEN ‘未婚‘ WHEN ‘yh‘ THEN ‘已婚‘ WHEN ‘ly‘ THEN ‘离异‘ WHEN ‘so‘ THEN ‘丧偶‘ END) has_married,
(CASE t2.gender WHEN ‘1‘ THEN ‘男‘ WHEN ‘0‘ THEN ‘女‘ ELSE ‘请选择‘ END) AS gender,
(CASE t2.max_degree WHEN ‘1‘ THEN ‘初中及一下‘ WHEN ‘2‘ THEN ‘高中‘ WHEN ‘3‘ THEN ‘专科‘ WHEN ‘4‘ THEN ‘本科‘ WHEN ‘5‘ THEN ‘硕士及以上‘ WHEN ‘0‘ THEN ‘请选择‘ END) max_degree,
(CASE t4.work_unit_property WHEN ‘gy‘ THEN ‘国营‘ WHEN ‘my‘ THEN ‘民营‘ WHEN ‘sy‘ THEN ‘私营‘ WHEN ‘sz‘ THEN ‘三资‘ WHEN ‘hh‘ THEN ‘合伙‘ WHEN ‘gt‘ THEN ‘个体‘ WHEN ‘qt‘ THEN ‘其他‘ ELSE ‘请选择‘ END) work_unit_property,
t5.comp_industry,
t4.work_unit_duty,
t6.house_address_city,
t6.house_address_district,
t6.house_address_province,
t6.house_building_area,
t8.rev_contact_number,
(CASE t8.credit_record_type WHEN ‘1‘ THEN ‘是‘ WHEN ‘0‘ THEN ‘否‘ ELSE ‘请选择‘ END) AS credit_record_type,
t8.rev_outstanding_loan,
t7.original_borrower_record,
(CASE t9.processing_form WHEN ‘320‘ THEN ‘罚款‘ WHEN ‘321‘ THEN ‘拘役管制‘ WHEN ‘322‘ THEN ‘劳动教养‘ WHEN ‘323‘ THEN ‘刑罚‘ ELSE ‘请选择‘ END) AS processing_form,
(CASE t9.involve_problem WHEN ‘317‘ THEN ‘不良行为‘ WHEN ‘319‘ THEN ‘刑事案件‘ WHEN ‘318‘ THEN ‘人身伤害‘ ELSE ‘请选择‘ END) AS involve_problem,
t10.execute_target,
(CASE t1.hasconmpre WHEN ‘302‘ THEN ‘单人贷‘ WHEN ‘303‘ THEN ‘共同2人‘ WHEN ‘304‘ THEN ‘3人以上‘ ELSE ‘请选择‘ END) AS ‘共贷情况‘,
(CASE t1.cre_loan_type WHEN ‘110‘ THEN ‘佳英贷‘ WHEN ‘111‘ THEN ‘佳楼贷‘ WHEN ‘112‘ THEN ‘佳薪贷‘ WHEN ‘113‘ THEN ‘佳业贷‘ ELSE ‘请选择‘ END)cre_loan_type,
t2.birthday,
t2.id_card,
t12.aver_balance,
t12.aver_payment,
t12.month_payment,
t13.unpay_loan_amount,
t13.unpay_loan_num,
t13.unpay_loan_balance,
t13.credit_card_total_amount,
t13.credit_card_most_amount,
t13.credit_have_amount,
t13.three_overdue_card_num,
t13.six_overdue_card_num,
t13.one_year_overdue_rate,
t13.two_year_overdue_rate,
t13.cur_overdue_card_amount,
t13.three_apply_time,
t13.six_apply_time,
t13.year_apply_time,
t13.guarantee_amount,
(CASE t13.ecurity_state WHEN ‘287‘ THEN ‘正常‘ WHEN ‘288‘ THEN ‘关注‘ WHEN ‘289‘ THEN ‘次级‘ WHEN ‘290‘ THEN ‘可疑‘ WHEN ‘291‘ THEN ‘损失‘ ELSE ‘请选择‘ END) ecurity_state,
t1.credit_purpose,
(CASE t14.borrower_quality WHEN ‘179‘ THEN ‘优质‘ WHEN ‘180‘ THEN ‘较好‘ WHEN ‘181‘ THEN ‘一般‘ WHEN ‘182‘ THEN ‘较差‘ ELSE ‘请选择‘ END)borrower_quality,
t14.max_repayment_limit_per_month,
(CASE t14.where_house_card WHEN ‘1‘ THEN ‘房产局‘ WHEN ‘2‘ THEN ‘本人‘ WHEN ‘3‘ THEN ‘调档‘ WHEN ‘4‘ THEN ‘银行‘ WHEN ‘5‘ THEN ‘未下来‘ ELSE ‘请选择‘ END) where_house_card,
t15.is_authenticity,
t15.evalu,
t15.is_coordination,
t15.contact_quality,
(CASE t14.couple_compensation WHEN ‘187‘ THEN ‘愿意‘ WHEN ‘188‘ THEN ‘不愿意‘ WHEN ‘189‘ THEN ‘无‘ ELSE ‘请选择‘  END) couple_compensation,
(CASE t14.parents_compensation WHEN ‘190‘ THEN ‘愿意‘ WHEN ‘191‘ THEN ‘不愿意‘ WHEN ‘192‘ THEN ‘无‘ ELSE ‘请选择‘  END)parents_compensation,
(CASE t14.children_compensation WHEN ‘193‘ THEN ‘愿意‘ WHEN ‘194‘ THEN ‘不愿意‘ WHEN ‘195‘ THEN ‘无‘ ELSE ‘请选择‘  END)children_compensation,
(CASE t14.couples_attitude WHEN ‘311‘ THEN ‘同意‘ WHEN ‘312‘ THEN ‘不同意‘ WHEN ‘313‘ THEN ‘不管‘ WHEN ‘314‘ THEN ‘无‘ ELSE ‘请选择‘ END) couples_attitude,
(CASE t14.parents_attitude WHEN ‘311‘ THEN ‘同意‘ WHEN ‘312‘ THEN ‘不同意‘ WHEN ‘313‘ THEN ‘不管‘ WHEN ‘314‘ THEN ‘无‘ ELSE ‘请选择‘ END) parents_attitude,
(CASE t14.children_attitude WHEN ‘311‘ THEN ‘同意‘ WHEN ‘312‘ THEN ‘不同意‘ WHEN ‘313‘ THEN ‘不管‘ WHEN ‘314‘ THEN ‘无‘ ELSE ‘请选择‘ END) children_attitude,
t14.other_loan_num,
t14.other_loan_account,
t14.comp_eval,
t14.review_comments,
t16.phone1_2,
t16.phone1_3,
t16.phone2_2,
t16.phone2_3,
t16.phone1_1,
t16.phone2_1,
t16.contact_relation_description,
(CASE t14.ds_health_situation WHEN ‘315‘ THEN ‘存在重大疾病‘ WHEN ‘316‘ THEN ‘无疾病‘ ELSE ‘请选择‘ END) ds_health_situation,
(CASE t14.ds_repay_ability WHEN ‘305‘ THEN ‘优质‘ WHEN ‘306‘ THEN ‘较好‘ WHEN ‘307‘ THEN ‘一般‘ WHEN ‘308‘ THEN ‘较差‘ WHEN ‘309‘ THEN ‘差‘ WHEN ‘310‘ THEN ‘无‘ ELSE ‘请选择‘ END) ds_repay_ability
FROM mcs_cre_credit_head t1 
LEFT JOIN mcs_cre_credit_line_customer_change_head t2 ON t2.mcs_cre_credit_head_id=t1.mcs_cre_credit_head_id
LEFT JOIN (SELECT b.mcs_cre_credit_line_customer_change_head_id,group_concat(h.house_address_city,‘‘) house_address_city,group_concat(h.house_address_district,‘‘) house_address_district,group_concat(h.house_address_province,‘‘) house_address_province,group_concat(h.house_building_area,‘‘) house_building_area 
from mcs_cre_customer_change_line_houseinfo h 
left join mcs_cre_credit_line_customer_change_head b
on h.mcs_cre_credit_line_customer_change_head_id = b.mcs_cre_credit_line_customer_change_head_id
GROUP BY b.mcs_cre_credit_head_id) t6 ON t6.mcs_cre_credit_line_customer_change_head_id=t2.mcs_cre_credit_line_customer_change_head_id
LEFT JOIN mcs_cre_rev_info_main t8 ON t8.mcs_cre_credit_head_id=t1.mcs_cre_credit_head_id
LEFT JOIN mcs_cre_customer_change_line_workinfo t4 ON t4.mcs_cre_credit_line_customer_change_head_id=t2.mcs_cre_credit_line_customer_change_head_id
LEFT JOIN (SELECT co.mcs_cre_credit_line_customer_change_head_id, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(co.comp_industry,‘jtysy‘,‘交通运输业‘),‘pflsy‘,‘批发零售业‘),‘fwy‘,‘服务业‘),‘jzy‘,‘建筑业‘),‘ny‘,‘农业‘),‘qt‘,‘其他‘) AS comp_industry FROM mcs_cre_customer_change_line_company co) t5 ON t5.mcs_cre_credit_line_customer_change_head_id=t2.mcs_cre_credit_line_customer_change_head_id
LEFT JOIN (SELECT r.mcs_cre_credit_head_id, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE((SELECT GROUP_CONCAT(re.original_borrower_record,‘‘) FROM mcs_cre_rev_borrower_record re WHERE mcs_cre_credit_head_id=r.mcs_cre_credit_head_id ),‘333‘,‘优质‘),‘334‘,‘较好‘),‘335‘,‘一般‘),‘336‘,‘较差‘),‘337‘,‘极差‘),‘-1‘,‘请选择‘) original_borrower_record FROM mcs_cre_rev_borrower_record r WHERE r.mcs_cre_credit_head_id=r.mcs_cre_credit_head_id) t7 ON t7.mcs_cre_credit_head_id=t1.mcs_cre_credit_head_id
LEFT JOIN (SELECT c.mcs_cre_credit_head_id,GROUP_CONCAT(c.processing_form,‘‘) processing_form,GROUP_CONCAT(involve_problem,‘‘) involve_problem from mcs_cre_rev_info_criminal c GROUP BY c.mcs_cre_credit_head_id) t9 ON t9.mcs_cre_credit_head_id=t1.mcs_cre_credit_head_id
LEFT JOIN (SELECT a.mcs_cre_credit_head_id,group_concat(execute_target,‘‘) execute_target FROM mcs_cre_rev_info_court_case a group by a.mcs_cre_credit_head_id) t10 ON t10.mcs_cre_credit_head_id=t1.mcs_cre_credit_head_id
LEFT JOIN mcs_cre_rev_water_model t12 ON t12.mcs_cre_credit_head_id=t1.mcs_cre_credit_head_id
LEFT JOIN mcs_cre_rev_certificate_model t13 ON t13.mcs_cre_credit_head_id=t1.mcs_cre_credit_head_id
LEFT JOIN mcs_cre_rev_phone_main t14 ON t14.mcs_cre_credit_head_id=t1.mcs_cre_credit_head_id
LEFT JOIN (SELECT DISTINCT r.mcs_cre_credit_head_id,REPLACE(REPLACE(REPLACE(REPLACE((SELECT GROUP_CONCAT(t.is_authenticity,‘‘) FROM mcs_cre_rev_phone_contact t WHERE t.mcs_cre_credit_head_id=r.mcs_cre_credit_head_id),‘-1‘,‘请选择‘),‘245‘,‘真实‘),‘246‘,‘虚假‘),‘247‘,‘未接通‘) is_authenticity,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE((SELECT GROUP_CONCAT(ev.evalu,‘‘) FROM mcs_cre_rev_phone_contact ev WHERE ev.mcs_cre_credit_head_id=r.mcs_cre_credit_head_id),‘,‘,‘请选择,‘),‘1‘,‘请选择‘),‘2‘,‘较好‘),‘3‘,‘一般‘),‘4‘,‘较差‘),‘5‘,‘未接通‘),‘6‘,‘无‘) evalu,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE((SELECT GROUP_CONCAT(co.is_coordination,‘‘) FROM mcs_cre_rev_phone_contact co WHERE co.mcs_cre_credit_head_id=r.mcs_cre_credit_head_id),‘,‘,‘请选择‘),‘1‘,‘配合‘),‘2‘,‘不配合‘),‘3‘,‘未接通‘),‘4‘,‘无‘) is_coordination,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE((SELECT GROUP_CONCAT(att.family_attitude,‘‘) FROM mcs_cre_rev_phone_contact att WHERE att.mcs_cre_credit_head_id=r.mcs_cre_credit_head_id),‘,‘,‘请选择‘),‘1‘,‘同意‘),‘2‘,‘不同意‘),‘3‘,‘不管‘),‘4‘,‘无‘)family_attitude,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE((SELECT GROUP_CONCAT(qu.contact_quality,‘‘) FROM mcs_cre_rev_phone_contact qu WHERE qu.mcs_cre_credit_head_id=r.mcs_cre_credit_head_id),‘,‘,‘请选择,‘),‘1‘,‘优质‘),‘2‘,‘较好‘),‘3‘,‘一般‘),‘4‘,‘较差‘),‘5‘,‘未接通‘),‘6‘,‘无‘) contact_quality FROM mcs_cre_rev_phone_contact r WHERE r.mcs_cre_credit_head_id=r.mcs_cre_credit_head_id GROUP BY r.mcs_cre_credit_head_id) t15 ON t15.mcs_cre_credit_head_id=t1.mcs_cre_credit_head_id
LEFT JOIN (SELECT c.is_major AS is_major,c.mcs_cre_credit_head_id,c.mcs_cre_credit_line_customer_change_head_id,GROUP_CONCAT(c.phone1_1,‘‘) phone1_1,GROUP_CONCAT(c.phone1_2) phone1_2,GROUP_CONCAT(c.phone1_3) phone1_3,GROUP_CONCAT(c.phone2_1) phone2_1,GROUP_CONCAT(c.phone2_2) phone2_2,GROUP_CONCAT(c.phone2_3) phone2_3,GROUP_CONCAT(contact_relation_description,‘‘) contact_relation_description FROM mcs_cre_customer_change_line_contact c WHERE is_major=1  AND mcs_cre_credit_head_id=c.mcs_cre_credit_head_id GROUP BY c.mcs_cre_credit_head_id) t16 ON t16.mcs_cre_credit_head_id=t1.mcs_cre_credit_head_id  
where t2.is_major=‘1‘ AND t2.enable_flag=‘1‘ AND t1.mcs_cre_credit_head_id=‘972‘;