MYSQL:同一张表上的两个连接变成双分组计数。如何解决？

Question

具有这种简化的结构:

customers
-----------------------
id
name
salesagents_id      (each client has their own sales agent assigned)

salesagents
-----------------------
id
name


visits
-----------------------
customers_id
salesagents_id
date

我需要获取所有客户的列表，以及三个附加字段:

1. 分配给客户的销售代理姓名
2. 销售人员接待的访问次数
3. 上个月收到的访问次数

这是我目前的查询:

SELECT clients.*, salesagents.name, COUNT(v1.id) as visits_number, COUNT(v2.id) as visits_number_last_month
FROM `clients`
LEFT JOIN `salesagents` ON `clients`.`salesagents_id`=`salesagents`.`id`
LEFT JOIN `visits` as `v1` ON `clients`.`id` = `v1`.`clients_id`
LEFT JOIN `visits` as `v2` ON `clients`.`id` = `v2`.`clients_id` AND `v2`.`date` > FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MONTH)))
GROUP BY `clients`.`id`

问题是同一张表上的两个连接构成了双倍的访问数。

我还能做什么？

Answer 1

Distinct Count 可以解决您的问题

SELECT clients.*, salesagents.name, COUNT(DISTINCT v1.id) as visits_number, COUNT( DISTINCT v2.id) as visits_number_last_month
FROM `clients`
LEFT JOIN `salesagents` ON `clients`.`salesagents_id`=`salesagents`.`id`
LEFT JOIN `visits` as `v1` ON `clients`.`id` = `v1`.`clients_id`
LEFT JOIN `visits` as `v2` ON `clients`.`id` = `v2`.`clients_id` AND `v2`.`date` > FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MONTH)))
GROUP BY `clients`.`id`