mysql多表leftjoin1对多的解决办法
⼀个表left join多个表并导出csv本⾝是很简单的事。但是主表的⼀列有多个值，⼀开始⽤逗号分隔保存数据，这样做的好处是⽅便应⽤程序处理，但是对于mysql来说这是anti-pattern的做法。果然，在写left join的时候⽆法实现⼀个field保存多ID，并跟关联表匹配输出csv⽂件。（尝试了find_in_set 只能输⼊⼀⾏）
还是应该遵守RMDB的范式来设计DB，将多值的列，拆分出⼀个新表来保存。这样写left join时就⽅便⼀对⼀处理。最后，要输出多个值时，多值会存在多列，可以采⽤GROUP_CONCAT() ... group by将多列值合并为⼀⾏。
最终的sql如下
CREATE VIEW edc_v_customer AS SELECT A.`id`,
A.`email`,
A.`mobile`, A.`first_name`,A.`last_name`,
A.`manggis_id`,
A.`external_id`,
GROUP_CONCAT(ST.item_value) as site,
A.`nick_name`,
sql left join 多表连接A.`chinese_name`,
S.`item_value` as sex,
A.`birthdate`,
A.`age`,
G.`item_value` as `age_group`,
I.`item_value` as income,
E.`item_value` as educate,
M.`item_value` as marial,
O.`item_value` as occupation,
C.`item_value` as country,
A.`update_time`,A.`status` FROM edc_customer AS A
LEFT JOIN edc_prop_country AS C ON A.`country_id`>0 and A.`country_id`=C.id
LEFT JOIN edc_customer_in_site AS CS ON (CS.customer_id=A.id)
LEFT JOIN edc_prop_site AS ST ON ST.id=CS.site_id
LEFT join `edc_prop_sex`  AS S on A.`sex_id`>0 and S.id=A.`sex_id`
LEFT join `edc_prop_occupation`  AS O on A.`occupation_id`>0 and O.id=A.`occupation_id`
LEFT join `edc_prop_marial`  AS M on A.`marial_id`>0 and M.id=A.`marial_id`
LEFT join `edc_prop_income`  AS I on A.`income_id`>0 and I.id=A.`income_id`
LEFT join `edc_prop_educate`  AS E on A.`educate_id`>0 and  E.id=A.`educate_id`
LEFT join `edc_prop_age_group`  AS G on A.`age_group_id`>0 and G.id=A.`age_group_id`
GROUP BY A.id
;
另外，要记得加上index，如果没有index，表数据⼤的话会⾮常慢，甚⾄拒绝响应。
默认导出csv⽂件是没有header的，为了增加header，可以选择“Custom - display all possible options”，勾选“Put columns names in the first row”

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。