获取同时从Sean和Jean处采购的客户姓名的SQL报错求助

首先，你的查询报错的核心原因是关联条件里引用了外部无法访问的表别名：在最后的ON C.CustomerID = A.CustomerID中，A是嵌套子查询里的表别名，外部查询根本识别不到它，应该换成派生表的别名CID对应的字段CID.CustomerID。

另外，虽然你的INTERSECT子查询逻辑是对的，但可以稍微简化一下写法。下面是修正后的完整SQL：

SELECT C.FirstName, C.LastName
FROM Customer C
INNER JOIN (
    SELECT DISTINCT A.CustomerID
    FROM Account A
    INNER JOIN Salesman S ON A.SalesmanNo = S.SalesmanNo
    WHERE S.empName = 'Sean'
    INTERSECT
    SELECT DISTINCT A.CustomerID
    FROM Account A
    INNER JOIN Salesman S ON A.SalesmanNo = S.SalesmanNo
    WHERE S.empName = 'Jean'
) AS CID ON C.CustomerID = CID.CustomerID;

额外优化方案：用GROUP BY+HAVING替代INTERSECT #

对于这种"同时满足多个条件"的需求，用GROUP BY统计的方式通常更高效，尤其是数据量较大的时候：

SELECT C.FirstName, C.LastName
FROM Customer C
INNER JOIN Account A ON C.CustomerID = A.CustomerID
INNER JOIN Salesman S ON A.SalesmanNo = S.SalesmanNo
WHERE S.empName IN ('Sean', 'Jean')
GROUP BY C.CustomerID, C.FirstName, C.LastName
HAVING COUNT(DISTINCT S.empName) = 2;

这个逻辑是：先筛选出和Sean、Jean相关的账户记录，然后按客户分组，只保留同时关联了两个销售员的客户（通过COUNT(DISTINCT S.empName) = 2确保两个销售员都存在）。

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