SQL查询
粘上我前段时间写的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';
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 '丧偶' ELSE '' 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, ( CASE t8.rev_outstanding_loan WHEN '331' THEN '是' WHEN '332' THEN '否' ELSE '' END ) 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 ) hasconmpre, ( CASE t1.cre_loan_type WHEN '110' THEN '佳英贷' WHEN '111' THEN '佳楼贷' WHEN '112' THEN '佳薪贷' WHEN '113' THEN '佳业贷' ELSE '' END ) cre_loan_type, t1.credit_limit, 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, splitStr(t15.is_authenticity,',',1) as ceshi1, splitStr(t15.is_authenticity,',',2) as ceshi2, splitStr(t15.is_authenticity,',',3) as ceshi3, splitStr(t15.is_authenticity,',',4) as ceshi4, splitStr(t15.is_authenticity,',',5) as ceshi5, splitStr(t15.is_authenticity,',',6) as ceshi6, 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, co.comp_industry 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, ( 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 ) 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 t.mcs_cre_credit_head_id,GROUP_CONCAT(t.is_authenticity, '') is_authenticity,GROUP_CONCAT(t.evalu, '') evalu,GROUP_CONCAT(t.is_coordination,'') is_coordination,GROUP_CONCAT(t.family_attitude,'') family_attitude,GROUP_CONCAT(contact_quality,'') contact_quality FROM mcs_cre_rev_phone_contact t WHERE t.mcs_cre_credit_head_id =mcs_cre_credit_head_id GROUP BY t.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 IN (1004, 967, 972, 971, 966);
创建函数：
CREATE FUNCTION splitStr(str VARCHAR(200),rep VARCHAR(200),num INT) RETURNS VARCHAR(200) BEGIN DECLARE substr VARCHAR(200); DECLARE resultStr VARCHAR(200); SET substr = SUBSTRING_INDEX(str,rep,num); SET resultStr = SUBSTRING_INDEX(substr,rep,-1); RETURN resultStr; END;
oracle分页：
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN (pageNum-1)*pageSize+1 AND pageNum*pageSize
CONCAT()函数的使用方法：CONCAT()函数是连接字符串，某字段连接某字段或者连接某格式的字符串
eg: select * from user;
result:
id name sex
1 张三 1
2 李四 0
使用concat()函数时：
select concat(name,'(',sex,')') as obj from user;
result:
obj
张三(1)
李四(0)
还可以这样做查询
SELECT CONCAT(user_name,',') as obj from user;
result:
obj
张三,
李四,
-- 求某科分数的平均分和及格率，涨姿势了！及格率=及格总人数/总人数*100 SELECT * FROM ( SELECT avg(score) AS '英语平均成绩', (sum( CASE WHEN score >= 60 THEN 1 ELSE 0 END ) / COUNT(*))*100 AS '英语及格率' FROM SC ) a;
MySQL查询时，请尽量少用子查询，子查询较多时请用联合查询，可以有40多秒的速度变成1秒不到。例如以下例子：
40秒的sql写法：
SELECT f.id AS ryid, f.dtrymc, m.hdbt, LEFT (y.hdkssj, 10) hdkssj, LEFT (y.hdjssj, 10) hdjssj, ( SELECT count(1) FROM shop_order WHERE promoterid = f.id AND orderstatus = '5' AND createtime >= '2016-06-17 00:00:00' AND createtime <= '2016-06-29 16:43:44' ) AS orderNums, ( SELECT sum(yjyds) FROM shop_order_goodmsg a, shop_order b WHERE a.orderid = b.id AND b.promoterid = f.id AND b.orderstatus = '5' AND b.createtime >= '2016-06-17 00:00:00' AND b.createtime <= '2016-06-29 16:43:44' ) AS tranNums, ( SELECT count(1) FROM shop_dthd_lljl z WHERE z.ryid = f.id ) AS browseNums FROM shop_dtfzb_ry f JOIN shop_dthd_dtry x ON f.id = x.ryid JOIN shop_dthd y ON x.dthdid = y.id JOIN shop_activey m ON y.activeyid = m.id JOIN shop_order g ON g.promoterid = f.id WHERE 1 = 1 and g.createtime >= '2016-06-17 00:00:00' AND g.createtime <= '2016-06-29 16:43:44' GROUP BY f.dtrymc, m.hdbt, y.hdkssj, y.hdjssj ORDER BY m.hdbt LIMIT 0, 20;
优化后使用联合查询后变化的sql：
SELECT f.id AS ryid, f.dtrymc, m.hdbt, LEFT (y.hdkssj, 10) hdkssj, LEFT (y.hdjssj, 10) hdjssj, s.orderNums, p.tranNums, z.browseNums FROM shop_dtfzb_ry f JOIN shop_dthd_dtry x ON f.id = x.ryid JOIN shop_dthd y ON x.dthdid = y.id JOIN shop_activey m ON y.activeyid = m.id left JOIN shop_order g ON g.promoterid = f.id left join ( SELECT count(1) orderNums,promoterid,activeyid FROM shop_order WHERE orderstatus = '5' AND createtime >= '2016-06-17 00:00:00' AND createtime <= '2016-06-29 16:43:44' group by promoterid,activeyid ) s on y.activeyid=s.activeyid and f.id=s.promoterid left JOIN ( SELECT sum(yjyds) tranNums,promoterid,a.activeyid FROM shop_order_goodmsg a, shop_order b WHERE a.orderid = b.id AND b.orderstatus = '5' AND b.createtime >= '2016-06-17 00:00:00' AND b.createtime <= '2016-06-29 16:43:44' group by promoterid,activeyid ) p on y.activeyid=p.activeyid and f.id=p.promoterid left join ( SELECT count(1) browseNums,ryid FROM shop_dthd_lljl z where 1=1 and llsj >= '2016-06-17 00:00:00' AND llsj <= '2016-06-29 16:43:44' group by ryid ) z on z.ryid = f.id WHERE 1 = 1 and g.createtime >= '2016-06-17 00:00:00' AND g.createtime <= '2016-06-29 16:43:44' GROUP BY f.dtrymc, m.hdbt, y.hdkssj, y.hdjssj ORDER BY m.hdbt LIMIT 0, 20;
变化惊人，只怪自己太年轻。
查询表里重复的记录：
select count(*) as count from shop_order group by phonenum having count>1;
exists的运用
mysql 加上一天 date_add(now(),interval 1 day); date_add(now(),interval -1 day)减去一天
减去一天还有一个函数:date_sub(now(),interval 1 day) ;day换成month表示月，year为年，week为周
mysql查询数据库数据大小
select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_length desc;
mysql 时间段查询
SELECT
*
FROM
table_name
WHERE 1=1
AND STARTDATE <= '2019-12-30'
AND ENDDATE >= '2019-06-21'
无理由。