mysql - 使用2个左连接不起作用但单独可以得到结果

Question

我有三个表，公司、用户和共享。我想统计一家公司的用户和份额，它们不相关。

可能有一行具有共享值(value)但不具有用户值(value)。所以我使用了左连接，我可以单独得到结果，但它不能一起工作。

这是我的查询:

SELECT c.name, count(u.company_id), count(s.company_id)
FROM company c
LEFT JOIN user u
ON c.id=u.company_id and u.company_id=337
WHERE u.company_id is NOT NULL 
LEFT JOIN share s
ON c.id=s.id AND s.company_id=337 
WHERE s.company_id is NOT NULL 

Answer 1

您需要在子查询中至少执行一项计数。否则，两个计数将相同，因为您只是计算结果叉积中的行数。

SELECT c.name, user_count, share_count
FROM company AS c
JOIN (SELECT company_id, COUNT(*) AS user_count
      FROM users
      GROUP BY company_id) AS u
ON u.company_id = c.id
JOIN (SELECT company_id, COUNT(*) AS share_count
      FROM share 
      GROUP BY company_id) AS s
ON s.company_id = c.id
WHERE c.company_id = 337

另一个选项是计算您要连接的表的不同主键:

SELECT c.name, COUNT(DISTINCT u.id) AS user_count, COUNT(DISTINCT s.id) AS share_count
FROM company AS c
JOIN users AS u on u.company_id = c.id
JOIN share AS s ON s.company_id = c.id
WHERE c.company_id = 337