任务:遍历表mk_docrov_followup,当表中的某些字段为空时,判断与其相关联的:mk_docrov_lifestyle,mk_docrov_auxex,mk_docrov_medical_use,mk_docrov_referral这四张表中的所有字段,当所有字段均为空时,则unfinish++,只要这四张表中有一个字段不为空,则不能做unfinish++的操作。
一开始用死方法,if和for,最后发现在遍历表mk_docrov_followup之后至少还要做16次的if判断,太麻烦,所以探寻新大陆:
1 试着写表连接
select *
FROM
(
`mk_docrov_followup` mkdf //一对一的表
)
LEFT JOIN
`mk_docrov_lifestyle` mklifestyle
ON (mklifestyle.`mk_docrov_followup_id` = mkdf.`id`) //一对多的表
LEFT JOIN
`mk_docrov_auxex` mkauxex
ON (mkauxex.`mk_docrov_followup_id` = mkdf.`id`)
LEFT JOIN
`mk_docrov_medical_use` mkmu
ON (mkmu.`mk_docrov_followup_id` = mkdf.`id`)
LEFT JOIN
`mk_docrov_referral` mkreferral
ON (mkreferral.`mk_docrov_followup_id` = mkdf.`id`)
成功生成一张齐全的表格
2 把需要做判断的字段选择出来
select mkdf.inspection_id AS a,mkdf.symptom_code AS b,
mkdf.symptom_other AS c,mkdf.mk_docrov_medcom AS d,mkdf.mk_docrov_adr AS e,mkdf.mk_docrov_control AS f,
mkdf.mk_docrov_hypoglycemia AS g,
mklifestyle.mk_docrov_smoke AS h,mklifestyle.mk_docrov_alcohol AS i,
mklifestyle.mk_docrov_sport AS j,mklifestyle.mk_docrov_meal AS k,
mklifestyle.mk_docrov_psyre AS l,mklifestyle.mk_docrov_combe AS m,mklifestyle.mk_docrov_salt AS n,
mklifestyle.mk_docrov_diet AS o,
mkauxex.mk_docrov_blood AS p,mkauxex.mk_docrov_other AS q,
mkmu.mk_docrov_medical_name AS r,mkmu.mk_docrov_method AS s,
mkreferral.mk_docrov_referral_reason AS t, mkreferral.mk_docrov_org AS u
FROM
(
`mk_docrov_followup` mkdf
)
LEFT JOIN
`mk_docrov_lifestyle` mklifestyle
ON (mklifestyle.`mk_docrov_followup_id` = mkdf.`id`)
LEFT JOIN
`mk_docrov_auxex` mkauxex
ON (mkauxex.`mk_docrov_followup_id` = mkdf.`id`)
LEFT JOIN
`mk_docrov_medical_use` mkmu
ON (mkmu.`mk_docrov_followup_id` = mkdf.`id`)
LEFT JOIN
`mk_docrov_referral` mkreferral
ON (mkreferral.`mk_docrov_followup_id` = mkdf.`id`)
实验成功后
需要做 字符串的拼接
select GROUP_CONCAT( // 字符拼接CONCAT
IFNULL(mkdf.inspection_id, ''),IFNULL(mkdf.symptom_code, ''), //因为每个字段都可能为空,所以要ifnull,否则会出错
IFNULL(mkdf.symptom_other, ''),IFNULL(mkdf.mk_docrov_medcom, ''),
IFNULL(mkdf.mk_docrov_adr, ''),IFNULL(mkdf.mk_docrov_control, ''),
IFNULL(mkdf.mk_docrov_hypoglycemia,''),
IFNULL(mklifestyle.mk_docrov_smoke, ''),
IFNULL(mklifestyle.mk_docrov_alcohol,'') ,
IFNULL(mklifestyle.mk_docrov_sport,''),
IFNULL(mklifestyle.mk_docrov_meal,''),
IFNULL(mklifestyle.mk_docrov_psyre,''),
IFNULL(mklifestyle.mk_docrov_combe,''),
IFNULL(mklifestyle.mk_docrov_salt,''),
IFNULL(mklifestyle.mk_docrov_diet, ''),
IFNULL(mkauxex.mk_docrov_blood, ''),
IFNULL(mkauxex.mk_docrov_other, ''),
IFNULL(mkmu.mk_docrov_medical_name, ''),
IFNULL(mkmu.mk_docrov_method, '') ,
IFNULL(mkreferral.mk_docrov_referral_reason, ''),
IFNULL(mkreferral.mk_docrov_org, '')
)AS followupstatstic
FROM
(
`mk_docrov_followup` mkdf
)
LEFT JOIN
`mk_docrov_lifestyle` mklifestyle
ON (mklifestyle.`mk_docrov_followup_id` = mkdf.`id`)
LEFT JOIN
`mk_docrov_auxex` mkauxex
ON (mkauxex.`mk_docrov_followup_id` = mkdf.`id`)
LEFT JOIN
`mk_docrov_medical_use` mkmu
ON (mkmu.`mk_docrov_followup_id` = mkdf.`id`)
LEFT JOIN
`mk_docrov_referral` mkreferral
ON (mkreferral.`mk_docrov_followup_id` = mkdf.`id`)
GROUP BY mkdf.id //主键,唯一标示的字符,id有几个,最终就会拼接出几个字符串。