在左联接时计算不同的值

Question

我有一个如下所示的表：

people:
+-----+--------+-----+
|  id |   name | age |
+-----+--------+-----+
|  25 | Alpha |  30 |
| 113 |   Beta |  21 |
|  10 |   Test |  19 |
+-----+--------+-----+

还有一个类似的：

table2:
+-----+-----------+--------------+
|  id |   company | candidate_id | 
+-----+-----------+--------------+-
|   1 |  Google |           10 |    
|  36 | Microsoft |          113 |    
| 137 |  Google |           10 |    
|   2 | ITCompany |           10 |    
+-----+-----------+--------------+-------+

我想用表2加入人们，这样我就可以找到每个唯一的个人id对应的DISTINCT公司的总数。我的最终结果应该是这样的：

+-----+--------+-----------+
|  id |   name | companies |
+-----+--------+-----------+
|  10 |   Test |         2 |
|  25 | Alpha |         0 |
| 113 |   Beta |         1 |
+-----+--------+-----------+

我如何为公司计数？

SELECT people.id, name, company
FROM people
LEFT JOIN reports on people.id = table2.people_id

Answer 1

您可以按people.id分组并计算不同的公司：

SELECT p.id, p.name, 
COUNT(DISTINCT r.company) companies 
FROM people p LEFT JOIN reports r
ON p.id = r.people_id
GROUP BY p.id;

我假设id是表people的主键。