首页 > 代码库 > SQL查询

SQL查询

粘上我前段时间写的sql语句

 

 

select distinctt1.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_abilityFROM 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_idLEFT 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 bon h.mcs_cre_credit_line_customer_change_head_id = b.mcs_cre_credit_line_customer_change_head_idGROUP 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_idLEFT JOIN mcs_cre_rev_info_main t8 ON t8.mcs_cre_credit_head_id=t1.mcs_cre_credit_head_idLEFT 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_idLEFT 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_idLEFT 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_idLEFT 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_idLEFT 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_idLEFT JOIN mcs_cre_rev_water_model t12 ON t12.mcs_cre_credit_head_id=t1.mcs_cre_credit_head_idLEFT JOIN mcs_cre_rev_certificate_model t13 ON t13.mcs_cre_credit_head_id=t1.mcs_cre_credit_head_idLEFT JOIN mcs_cre_rev_phone_main t14 ON t14.mcs_cre_credit_head_id=t1.mcs_cre_credit_head_idLEFT 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_idLEFT 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;

 

无理由。

SQL查询