如何统计MySQL表中指定员工与其他员工的共同公司数量？

首先咱们得先明确数据表结构，假设你的表叫employee_companies，存的是员工和对应的公司关系，字段就是employee_name（员工姓名）和company_name（公司名称）。我先给个和你描述场景匹配的示例数据：

CREATE TABLE employee_companies (
    employee_name VARCHAR(50),
    company_name VARCHAR(50)
);

INSERT INTO employee_companies VALUES
('John', 'IBM'),
('John', 'Uber'),
('John', 'Walmart'),
('Sharon', 'IBM'),
('Sharon', 'Uber'),
('Sharon', 'Google'),
('Matt', 'Walmart'),
('Matt', 'Apple'),
('Carl', 'Microsoft');

接下来就是核心的查询语句了，我用CTE（公共表表达式）来实现，逻辑清晰还容易维护：

WITH target_companies AS (
    -- 先拿到目标员工（这里是John）的所有公司
    SELECT company_name
    FROM employee_companies
    WHERE employee_name = 'John'
)
SELECT 
    ec.employee_name,
    -- 统计每个员工和目标员工的共同公司数，没匹配到就是0
    COUNT(tc.company_name) AS common_companies_count
FROM employee_companies ec
-- 左连接保证所有员工都被统计，哪怕没有共同公司
LEFT JOIN target_companies tc ON ec.company_name = tc.company_name
-- 排除目标员工自己，避免统计到他自己
WHERE ec.employee_name != 'John'
GROUP BY ec.employee_name
-- 按共同公司数从多到少排序，方便查看
ORDER BY common_companies_count DESC;

执行这个查询后，你就能得到和预期完全一致的结果：

如果需要动态切换目标员工，不用每次改SQL，可以用MySQL的用户变量来实现，这样更灵活：

-- 设置目标员工变量
SET @target_employee = 'John';

WITH target_companies AS (
    SELECT company_name
    FROM employee_companies
    WHERE employee_name = @target_employee
)
SELECT 
    ec.employee_name,
    COUNT(tc.company_name) AS common_companies_count
FROM employee_companies ec
LEFT JOIN target_companies tc ON ec.company_name = tc.company_name
WHERE ec.employee_name != @target_employee
GROUP BY ec.employee_name
ORDER BY common_companies_count DESC;

逻辑说明 #

1. CTE target_companies：先筛选出目标员工的所有公司，相当于建立一个"基准公司列表"。
2. 左连接：把所有员工的公司记录和基准列表做匹配，这样哪怕某个员工没有任何共同公司，也会被保留在结果里。
3. COUNT统计：COUNT(tc.company_name)只会统计匹配成功的行数（也就是共同公司的数量），因为未匹配的记录中tc.company_name是NULL，COUNT不会计入NULL值，刚好得到0的结果。

内容的提问来源于stack exchange，提问作者rtorres