两个带有左连接的 COUNTS 相乘

Question

我正在尝试获取一个查询，该查询为我提供表 Users 中的用户名、用户从表 Job2User 中拥有的作业数以及用户从表 Place2User 中拥有的地点数。

以下查询不断将两个计数列的值相乘。例如，如果对于用户 1，作业计数应为 2，位置计数应为 4，则用户 1 行中的两列都将显示“8”。我不确定我做错了什么：

SELECT `UserName`, COUNT(`Job2User`.`UserID`), COUNT(`Place2User`.`UserID`) 
FROM `Users`
LEFT JOIN `Job2User` ON `Job2User`.`UserID`=`Users`.`UserID` 
LEFT JOIN `Place2User` ON `Place2User`.`UserID`=`Users`.`UserID` 
GROUP BY `UserName`;

Answer 1

您应该使用 count(distinct ...) 来计算唯一值。不是对 userid（外键）进行计数，而是对引用表的主键进行计数。

参阅此处的文档

请 得到 8 个，因为您从作业返回 2 条记录，从地点返回 4 条记录。由于您没有计算不同的值，因此您得到 2*4 = 8。

Answer 2

您的问题可能是您没有映射 Place2User 和 Job2User 表，因此您正在执行交叉联接。 有关交叉联接的详细信息

您将需要使用内部查询来实现此目的除非您这两个表 Place2User 以某种方式与 Job2User 相关。

试试这个：

SELECT `UserName`, `Job2User`.`Count`, `Place2User`.`Count` 
FROM `Users`
LEFT JOIN (SELECT `UserID`, COUNT(1) AS 'Count' FROM `Job2User` GROUP BY `UserID`) `Job2User` ON `Job2User`.`UserID`=`Users`.`UserID` 
LEFT JOIN (SELECT `UserID`, COUNT(1) AS 'Count' FROM `Place2User` GROUP BY `UserID`) `Place2User` ON `Place2User`.`UserID`=`Users`.`UserID` 

Answer 3

一种选择是对要计数的每个表使用内联视图

SELECT `UserName`, `Job2User`.`Job2UserCount`, `Place2User`.`Place2UserCount`
FROM `Users`
 ` 
LEFT JOIN
(SELECT

 COUNT(`Job2User`.`UserID`) Job2UserCount ,
 UserID
FROM
 Job2User
 GROUP BY 
  `UserID` )Job2User
ON `Job2User`.`UserID`=`Users`.`UserID
LEFT JOIN
(SELECT

 COUNT(`Place2User`.`UserID`) Place2UserCount,
 UserID
FROM
 Job2User
 GROUP BY 
  `UserID` )Place2User
ON `Place2User`.`UserID`=`Users`.`UserID` 
GROUP BY `UserName`; 

Answer 4

我看到的第一个问题是你的连接向后。您正在进行左联接，但用户表位于该联接的右侧。您需要像这样进行切换：

SELECT `UserName`, COUNT(`Job2User`.`UserID`), COUNT(`Place2User`.`UserID`) 
FROM `Users`
LEFT JOIN `Job2User` ON `Users`.`UserID`=`Job2User`.`UserID` 
LEFT JOIN `Place2User` ON `Users`.`UserID` =`Place2User`.`UserID`
GROUP BY `UserName`;

由于您正在对未链接时将为 NULL 的字段进行计数，因此这可能会起作用（抱歉已经晚了，我的大脑有点模糊）。如果没有，您可以采用以下方法：

SELECT UserName, 
  (SELECT COUNT(Job2User.UserID) 
   FROM Job2User 
   WHERE Job2User.UserID = t.UserID) AS Job2UserCount,
  (SELECT COUNT(Place2User.UserID) 
   FROM Place2User 
   WHERE Place2User.UserID = t.UserID) AS Place2UserCount
FROM Users AS t

Answer 5

好吧，对于每个 Job2User 来说，获取所有 Place2user 很简单，这就是为什么它会成倍增加。你应该在子查询中计算它然后使用左连接。