如何正确查询同一公司身兼CEO与Owner职位的人员信息？

一、原方法的合规性判断 #

你的SQL逻辑在数据无重复职位记录的前提下是有效的：通过过滤出Owner（1）和CEO（2）的职位记录，按公司+人员分组后，COUNT(*) > 1能筛选出同时拥有两个职位的人员。但存在两个明显的局限性：

1. 语法兼容性问题：在MySQL开启ONLY_FULL_GROUP_BY严格模式时，SELECT中的com.name和p.name不在GROUP BY列表中，会触发语法报错（虽然逻辑上company_id对应唯一公司名、person_id对应唯一人名，但不符合SQL标准）。
2. 重复记录干扰：如果company_person表中存在同一人同一公司同一职位的重复插入（比如两条Owner记录），COUNT(*) > 1会误判这类人员为同时拥有两个职位，导致结果不准确。

二、更优的实现方式 #

方案1：修正分组逻辑，避免重复记录干扰 #

用COUNT(DISTINCT position_id)替代COUNT(*)，同时将com.name和p.name加入分组列表，兼容严格SQL模式：

SELECT
    com.name AS company_name,
    p.name AS person_name
FROM `company_person` 
INNER JOIN companies com ON com.id = company_id
INNER JOIN people p ON p.id = person_id
WHERE position_id IN(1, 2) -- 1=Owner, 2=CEO
GROUP BY company_id, person_id, com.name, p.name
HAVING COUNT(DISTINCT position_id) = 2;

方案2：双表关联，精准匹配双职位 #

逻辑更直观，直接匹配同一公司同一人同时拥有Owner和CEO职位的记录，完全不受重复数据影响：

SELECT
    com.name AS company_name,
    p.name AS person_name
FROM company_person cp_owner
INNER JOIN company_person cp_ceo 
    ON cp_owner.company_id = cp_ceo.company_id 
    AND cp_owner.person_id = cp_ceo.person_id
INNER JOIN companies com ON com.id = cp_owner.company_id
INNER JOIN people p ON p.id = cp_owner.person_id
WHERE cp_owner.position_id = 1  -- Owner
  AND cp_ceo.position_id = 2;   -- CEO

如果在company_person表上创建(company_id, person_id, position_id)联合索引，该查询的性能会非常出色。

方案3：窗口函数实现（MySQL 8.0+） #

适合需要扩展多职位筛选的场景，通过窗口函数统计人员在目标职位中的数量：

SELECT DISTINCT
    com.name AS company_name,
    p.name AS person_name
FROM (
    SELECT
        company_id,
        person_id,
        COUNT(DISTINCT position_id) OVER (PARTITION BY company_id, person_id) AS pos_count
    FROM company_person
    WHERE position_id IN (1, 2)
) cp
INNER JOIN companies com ON com.id = cp.company_id
INNER JOIN people p ON p.id = cp.person_id
WHERE cp.pos_count = 2;

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