mysql中的case when then,replace的用法。

SELECT ys.person_id,a.name,CASE WHEN ys.gender='1' THEN '男' ELSE '女' END,YEAR(CURDATE())-YEAR(ys.birth_date) AS age,ys.id_card_code,ys.birth_date,CASE ys.edu WHEN 1 THEN '文盲及半文盲' WHEN 2 THEN '小学' WHEN 3 THEN '初中' WHEN 4 THEN '高中/技校/中专' WHEN 5 THEN '大学专科及以上' WHEN 6 THEN '不详' END,a.address,CASE ys.job WHEN 1 THEN '国家机关、党群组织、企业、事业单位负责人' WHEN 2 THEN '专业技术人员' WHEN 3 THEN '办事人员和有关人员' WHEN 4 THEN '商业、服务业人员' WHEN 5 THEN '农、林、牧、渔、水利业生产人员' WHEN 6 THEN '生产、运输设备操作人员及有关人员' WHEN 7 THEN '军人' WHEN 8 THEN '不便分类的其他从业人员' WHEN 9 THEN '待业' WHEN 0 THEN '空' END,ys.disease,m.bp_left_h,m.bp_left_l,m.bmi,m.waist_line,CASE m.exercise_frequency WHEN '1' THEN '每天' WHEN '2' THEN '每周一次以上' WHEN '3' THEN '偶尔' WHEN '4' THEN '不锻炼' END,m.each_exercise_time,m.adhere_exercise_time,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.adhere_exercise_style,'1','散步'),'2','慢跑'),'3','长跑'),'4','单杠'),'5','游泳'),'6','跳绳'),'7','舞蹈'),'8','球类运动'),'9','气功'),'a','太极'),'b','体操'),'c','其它'),m.exercise_other,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.diet,'1','荤素均衡'),'2','荤食为主'),'3','素食为主'),'4','嗜盐'),'5','嗜油'),'6','嗜糖') AS diet,CASE m.smoking_status WHEN '1' THEN '从不吸烟' WHEN '2' THEN '已戒烟' WHEN '3' THEN '吸烟' END,m.smoking_day,m.age_started_smoking,m.age_quit_smoking,case m.drinking_frequency when '1' then '从不' when '2' then '偶尔' when '3' then '经常' when '4' then '每天' end,m.drinking_day,case m.alcoholics when '1' then '未戒酒' when '2' then '已戒酒' end,m.alcoholics_age,m.age_started_drinking,case m.drunk when '1' then '是' when '2' then '否' end,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.drinking_category,'1','白酒'),'2','啤酒'),'3','红酒'),'4','黄酒 '),'5','其他'),m.drinking_category_r,m.fbg,m.hemoglobin,m.leucocyte,m.platelet,m.routine_other,m.proteinuria,m.urine_sugar,m.urine_ketone,m.urine_occult,m.urinalysis_other,m.microalbuminuria,CASE m.stool_occult WHEN  '1' THEN '阴性' when '2' then '阳性' END,m.sgpt,m.sgot,m.albumin,m.total_bilirubin,m.combined_bilirubin,m.serum_creatinine,m.bun,m.spc,m.sodium_concentration,m.tc,m.tg,m.ldlc,m.hdlc,m.glycohemoglobin,CASE m.hepatitis WHEN '1' THEN '阴性' when '2' then '阳性' END,m.hepatitis_remark,CASE m.ecg  WHEN '1' THEN '正常' when '2' then '异常' END,m.ecg_remark,CASE m.chest_xray WHEN '1' THEN '正常' when '2' then '异常' END,m.chest_xray_remark,CASE m.bray WHEN '1' THEN '正常' when '2' then '异常' END,m.bray_remark,CASE m.cervical_smear WHEN '1' THEN '正常' when '2' then '异常' END,m.cervical_smear_remark,m.auxiliary_other,case when length(m.health_evaluation_r)=0 then '体检无异常' else m.health_evaluation_r end FROM ys_base_info AS ys LEFT JOIN archives AS a ON ys.person_id = a.id LEFT JOIN medical m ON a.id = m.person_id WHERE (m.counter=(SELECT MAX(counter) FROM medical WHERE person_id = a.id) and ys.isReported='1' AND (m.fbg <7.0 OR m.fbg is null) AND ys.disease NOT LIKE '%糖尿病%') AND ((ys.gender='1' AND m.waist_line >=90) OR (ys.gender='2' AND m.waist_line >=85) OR (m.bmi>=24) OR (ys.disease LIKE '%冠心病%' OR ys.disease LIKE '%脑卒中%' OR ys.disease LIKE '%高血压%') OR (YEAR(CURDATE())-YEAR(ys.birth_date)>=40) OR (m.exercise_frequency<>'1' or m.exercise_frequency is null) OR (m.exercise_frequency='1' AND m.each_exercise_time<=30) OR (m.tc>=5.18 or m.tg >= 2.26)) and ys.isReported='1' and (YEAR(CURDATE())-YEAR(ys.birth_date)>18) and DATE_FORMAT(m.create_date,'%Y')='2013'