mysql - SQL 查询计算具有基于另一个列值的公共(public)列值的行

Question

我有一个包含此数据的 MySQL 表:

CREATE TABLE job_history(
  id INT PRIMARY KEY AUTO_INCREMENT,
  employee VARCHAR(50),
  company VARCHAR(50)
);

INSERT INTO job_history(employee, company) VALUES
('John', 'IBM'),
('John', 'Walmart'),
('John', 'Uber'),
('Sharon', 'IBM'),
('Sharon', 'Uber'),
('Matt', 'Walmart'),
('Matt', 'Starbucks'),
('Carl', 'Home Depot');

SELECT * FROM job_history;
+----+----------+------------+
| id | employee | company    |
+----+----------+------------+
|  1 | John     | IBM        |
|  2 | John     | Walmart    |
|  3 | John     | Uber       |
|  4 | Sharon   | IBM        |
|  5 | Sharon   | Uber       |
|  6 | Matt     | Walmart    |
|  7 | Matt     | Starbucks  |
|  8 | Carl     | Home Depot |
+----+----------+------------+

Here's the corresponding SQL Fiddle

我想创建一个 SQL 查询来计算表中给定员工和其他员工之间共同公司的数量。

例如，如果我想定位员工“John”，我希望得到这样的结果:

Sharon: 2
Matt: 1
Carl: 0

因为 Sharon 与 John 有 2 家共同公司(IBM 和 Uber)，Matt 与 John 有 1 家共同公司(沃尔玛)，而 Carl 与 John 有 0 家共同公司。

我该怎么做？

Answer 1

首先，您需要一个左联接——因为您需要所有员工，即使是那些没有共同公司的员工。其次，group by 获取计数:

select jh.employee, count(jh_john.company) as num_in_common
from job_history jh left join
     job_history jh_john
     on jh_john.company = jh.company and
        jh_john.employee = 'John'
where jh.employee <> 'John'
group by jh.employee;

注意:如果表中可能有重复项，则使用 count(distinct) 而不是 count()。