18 2021-01

MySQL使用多个left join后在count字段数据错误

mysql, 后端开发, 编程技术你是第2189个围观者 0条评论供稿者：陈建鑫标签：mysql优化, MySQL小知识

MySQL的并表一般都是在数据统计的时候用到比较多，最近做了个项目使用统计报表，多个表使用了多对多的数据库结构，结果在left join的时候，关联的左表字段为多，所以使用count的时候数据不准确

使用的脚本是：

SELECT
	`ads`.`custom_province` AS '省份ID',
	count(   ads.id ) AS '统计的数据' 
FROM
	`activity_details` `acd`
	LEFT JOIN `address` `ads` ON `acd`.`address_id` = `ads`.`id`
	LEFT JOIN `materials_configs` `mcon` ON `acd`.`activity_id` = `mcon`.`activity_id` 
WHERE
	( mcon.materials_lists_id IN ( 6, 7, 10, 11 ) ) 
	AND ( ads.activity_id = 26   ) 
GROUP BY
	`ads`.`custom_province`

得出结果：

结果都是需要是1 但是materials_configs数据库是多的关系，所以统计了4次表，这个情况需要添加关键词 DISTINCT

修改后的代码如下：

Source code

SELECT
	`ads`.`custom_province` AS '省份ID',
	count( DISTINCT  ads.id ) AS '统计的数据' 
FROM
	`activity_details` `acd`
	LEFT JOIN `address` `ads` ON `acd`.`address_id` = `ads`.`id`
	LEFT JOIN `materials_configs` `mcon` ON `acd`.`activity_id` = `mcon`.`activity_id` 
WHERE
	( mcon.materials_lists_id IN ( 6, 7, 10, 11 ) ) 
	AND ( ads.activity_id = 26   ) 
GROUP BY
	`ads`.`custom_province`