mysql ando_mysql多表left join 1对多的解决办法

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`,

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”

分享到：

2016-12-13 09:52

浏览 3166

分类:数据库

评论