如何使用mysql内部连接计算其他表中的行数？

vu8f3i0k 于 2021-06-19 发布在 Mysql

关注(0)|答案(2)|浏览(308)

我有以下三个表：
crm\U客户：

crm\U进度：

crm\U通知：

我想要以下结果：

为了实现上述所需的输出，我使用以下查询：

SELECT `cc`.`customer_id`, `cc`.`customer_name`, COUNT(`cp`.`progress_customer`) AS Progress, COUNT(`cn`.`notification_customer`) AS Notifications 
FROM `crm_customers` AS `cc` INNER JOIN `crm_progress` AS `cp` 
ON `cc`.`customer_id` = `cp`.`progress_customer` 
INNER JOIN `crm_notifications` AS `cn` 
ON `cn`.`notification_customer` = `cp`.`progress_customer` 
WHERE `cc`.`customer_id` = 1 AND `cc`.`customer_status` = 1

但结果却是完全错误的。但是，当我只连接两个表时，结果是准确的。
以下查询产生正确的输出：

SELECT `cc`.`customer_id`, `cc`.`customer_name`, COUNT(`cp`.`progress_customer`) AS Progress 
FROM `crm_customers` AS `cc` INNER JOIN `crm_progress` AS `cp` 
ON `cc`.`customer_id` = `cp`.`progress_customer` 
WHERE `cc`.`customer_id` = 1 AND `cc`.`customer_status` = 1

我哪里做错了？如何正确使用join获取所需结果？

mysql

来源：https://stackoverflow.com/questions/52419828/how-to-count-number-of-rows-in-other-tables-using-mysql-inner-join

2条答案

按热度按时间

kb5ga3dv1#

可通过以下查询实现：

SELECT `cc`.`customer_id`, `cc`.`customer_name`, COUNT(*) AS Progress, (SELECT COUNT(*) AS Notifications 
FROM `crm_customers` AS `cc` 
INNER JOIN `crm_notifications` AS `cn` ON `cc`.`customer_id` = `cn`.`notification_customer` 
WHERE `cc`.`customer_id` = 1 AND `cc`.`customer_status` = 1 
GROUP BY `cc`.`customer_id`) AS Notifications 
FROM `crm_customers` AS `cc` 
INNER JOIN `crm_progress` AS `cp` ON `cc`.`customer_id` = `cp`.`progress_customer` 
WHERE `cc`.`customer_id` = 1 AND `cc`.`customer_status` = 1 
GROUP BY `cc`.`customer_id`

赞(0）回复(0）举报 2021-06-20

6jygbczu2#

尝试下面的查询：您需要统计与某个客户相关联的进度标识和通知标识

SELECT `cc`.`customer_id`, `cc`.`customer_name`, COUNT(distinct `cp`.`progress_id`) AS Progress, COUNT(distinct `cn`.`notification_id`) AS Notifications 
FROM `crm_customers` AS `cc` INNER JOIN `crm_progress` AS `cp` 
ON `cc`.`customer_id` = `cp`.`progress_customer` 
INNER JOIN `crm_notifications` AS `cn` 
ON `cn`.`notification_customer` = `cp`.`progress_customer` 
WHERE `cc`.`customer_id` = 1 AND `cc`.`customer_status` = 1 
group by `cc`.`customer_id`, `cc`.`customer_name`

赞(0）回复(0）举报 2021-06-19

我来回答

如何使用mysql内部连接计算其他表中的行数？

2条答案

相关问题

热门标签

最新问答